Sql Search Statement
Dec 12, 2004
I'm searching a table based on 4 possible search criteria...city, name, date and type. The search can use just one of the search criteria or any combination of the four...i.e. city and name or city and type or name, date and type, etc.
The statement for one search criteria could be written like the following:
Select *
From dbo.myTable
Where field = 'myvar'
myvar = Request.Form("fieldname")
What would be the way to construct the statement to allow searching for any combination of the 4 search criteria?
Thanks in advance,
-Dman100-
View 9 Replies
ADVERTISEMENT
May 4, 2004
I am developing a simple search project against “Job Advertise� table on SQL server 2000. I create search page which has a textbox to input search keywords, and a listbox can multiple select job location. The second page request these input parameter and as a SQL statement to retrieve the search results. The search results show on Datagrid of second page.
I test the search, the SQL Statement as below:
SELECT AdID, JobTitle, JobCompany, JobLocationCity, JobLocationState,
JobLocationProvince, JobLocationCountry, AdPostDate FROM JobAd
WHERE AdActive = '1' AND AdExpireDate >= ‘4/29/2004’ AND JobKeyWords
LIKE '%computer%' OR JobTitle LIKE '%computer%' AND JobLocationCountry
= 'USA' AND JobLocationState = 'NJ' OR JobLocationState = 'NM' OR
JobLocationState = 'NY' ORDER BY JobTitle
The problem are: 1, Some Ad that expiredate < DateTime.Today and AdActive = 0 still show on Datagrid. 2, Some AdLocationCountry = ‘Canada’ show on Datagrid. 3, Some Ad no relation with computer are still retrieved.
I think because after Where, there are many OR condition, so cause those problems. How can I solve those problems?
I think maybe I should do (SELECT AdID, JobTitle, JobCompany, JobLocationCity, JobLocationState, JobLocationProvince, JobLocationCountry, AdPostDate FROM JobAd
WHERE AdActive = '1' AND AdExpireDate >= DateTime.Today) first and put results in temp one table, then do search the Keyword against temp one table and put the results into temp two table, then so search the Job Location against temp two table, finish by get the results to bind to Datagrid. If I should do this, how to write the SQL statement? Dose has a better way?
Anyone who have experience on this part, please give me a suggestion and help.
View 4 Replies
View Related
Jun 25, 2003
I’m trying to conduct a search on the sql table to find a field that is blank within a specific time frame. The search I tried was
SELECT *
FROM Receiving
WHERE (RecipientLastName = N’ ‘)
AND (RecDate >= ‘03262002’) AND (RecDate < ‘12062002’)
But I get an error when I try this, Now I also tried the same search but left off the last “AND” statement (AND (RecDate < ‘12062002’)) and I got back a ton of entries. Is there a way I can narrow this search down? Thanks
View 3 Replies
View Related
Sep 7, 2005
Hi i want to do a SQL statement where if i enter a word for example 'SQL' then it will display all the info that match with the word SQL from the entire database.
I will have something like this
SELECT * FROM (?) LIKE (my textbox)
What should i put after FROM as i want to select the ENTIRE database. Is there something like * for All?
Thanks
View 3 Replies
View Related
Oct 11, 2006
I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:
Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’
I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
“LS11 0ES”...
If I search for LS11, nothing is returned;
If I search for LS11%, nothing is returned;
If I search for %LS11%, nothing is returned;
If I search for %LS11%%, the data is returned.
If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).
However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.
If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.
Any ideas?
Thanks
View 3 Replies
View Related
Jul 20, 2005
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
View 4 Replies
View Related
Feb 28, 2008
Hi ALL,
How could you make the SQL Select statement query case sensitive, what i mean is i have this select statement below ...
Select * From Staff WHERE Staff.Staff_ID = 'KabirJ' AND Staff.Password = 'KaBir1!'
and a data row in the staff table that is the following information
Staff_ID Password
Kabirj kabir1!
Since the cases in database is different then in the string provided, i want sql to return me nothing, How do i go about and achieve that???????????
Thanks
Kabir
View 2 Replies
View Related
Feb 27, 2015
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it
Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
View 1 Replies
View Related
Dec 14, 2004
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?
What I use currently:
Dim strSQL As String
strSQL = "SELECT ArticleID, "
strSQL &= "ISNULL(Body, '') AS Body, "
strSQL &= "ISNULL(Subject, '') AS Subject "
strSQL &= "FROM Articles "
strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') "
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlDataAdapter(strSQL, con)
cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword
...
I'm not so sure where should I place the letter "N", I use :
SELECT ArticleID,
ISNULL(Body, '') AS Body,
ISNULL(Subject, '') AS Subject,
FROM Articles
WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')
in Query Analzyer, it works! But it failed in my program... oh my god...
Thanks a lot!
View 4 Replies
View Related
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Jan 26, 2007
I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234 so that it's only matching the last part
Any Help Would be greatly appreciated Thanks
View 6 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Jul 21, 2015
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
View 4 Replies
View Related
Nov 6, 2014
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
View 0 Replies
View Related
Jul 24, 2007
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think?
View 1 Replies
View Related
Mar 30, 2013
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
View 14 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related
Sep 14, 2004
Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.
Example:
Say I want to find "Terminator" (1984).
I want to be able to use "Term" or "ator" as search results and still return the proper record.
Here is my Stored Procedure creation sql:
CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@searchTerm*"')
GO
--- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms.
I am trying to accomplish what is similarly done with LIKE '%term%'.
thanks, YM
View 1 Replies
View Related
Mar 25, 2015
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
View 0 Replies
View Related
Jul 6, 2007
I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;
But it works if i do
select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.
View 7 Replies
View Related
Nov 1, 2015
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999
I am trying this query:
select * from Mytable where
contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where
MyCOlumn like '123.345.%';
View 12 Replies
View Related
Mar 1, 2007
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies
View Related
Nov 22, 2007
hi there,
i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out
can anyone help?
thanks
View 6 Replies
View Related
Apr 2, 2004
for example:
SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')
would always slower than
SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'
Why? and how can it be solved? can you help me?
:)
View 6 Replies
View Related
Dec 6, 2011
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g.
megger
meg
mft megger (proper order in the database is megger mft)
mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code:
<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme") <> "") Then
RSResults__param5 = Request("searchme")
End If
[Code] ....
View 1 Replies
View Related
Jun 24, 2015
I'm experiencing an anomaly with my Full text index.
Setup : SQL Server 2005
Indexed Table:
Assets
ID - int (PK, auto increment)
Ref - varchar(50)
[code]....
I have re-built the full-text index, no change.
View 2 Replies
View Related
Jan 24, 2006
Are there any big differences between the two search techniques? It seems like they are both very similar.
SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%'
SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')
View 2 Replies
View Related
Mar 14, 2007
Hi guys
I need to create an "advanced search" which will allow the user to narrow down his results.
Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing.
Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large.
Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc?
Many Thanks
John
View 2 Replies
View Related
Apr 14, 2008
--set ANSI_NULLS ON
--set QUOTED_IDENTIFIER ON
--go
--
--
--ALTER PROC [dbo].[spEventTagCloud]
--as
--BEGIN
DECLARE @RECORDCOUNT INT;
DECLARE @SearchString varchar(2000);
DECLARE @QRY VARCHAR(2000);
DECLARE @SE VARCHAR(2000);
SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTS
DECLARE @ST INT;
SET @ST=1;
CREATE TABLE #TEMP2
(
MYTAGS VARCHAR(2000)
)
--CREATE TABLE #TEMP3
--(
-- TAGCOUNT INT
--)
CREATE TABLE #TEMP1
(
STR1 VARCHAR(2000)
)
WHILE @ST<@RECORDCOUNT
BEGIN
SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS'
--PRINT @QRY
INSERT INTO #TEMP1 EXEC (@QRY)
SELECT @SEARCHSTRING=STR1 FROM #TEMP1
SET @ST=@ST+1
declare @i1 int;
declare @i2 int;
declare @MatchType int ;
set @MatchType=0;
declare @Word varchar(100);
declare @Words table (Word varchar(100) not null);
declare @WordCount as integer;
DECLARE @TEMPWORD VARCHAR(2000);
begin
set nocount on
if (@MatchType != 2)
begin
set @SearchString = ' ' + @SearchString + ',';
--print 'Search String is :::: '+ @SearchString
set @i1 = 1;
while (@i1 != 0)
begin
set @i2=charindex(',', @SearchString, @i1+1)
--print @i1
if (@i2 != 0)
begin
set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
SET @TEMPWORD=@WORD;
SET @TEMPWORD=REPLACE(@TEMPWORD,',','')
INSERT INTO #TEMP2 SELECT @TEMPWORD
--print 'Search WORD is :::: '+ @WORD
if @Word != '' insert into @Words select replace(@Word,',','')
end
set @i1 = @i2
end
end
else
insert into @Words select ltrim(rtrim(@SearchString))
set @WordCount = (select count(*) from @Words)
Declare @wordtemp varchar(2000);
set @wordtemp=@word
set @wordtemp=replace(@word,',','')
--INSERT INTO #TEMP2 SELECT @WORDtemp
END
END
SELECT mytags'Tag' , count(mytags)'Count' FROM #TEMP2 group by mytags ORDER BY [COUNT] DESC
--SELECT * FROM #TEMP1
DROP TABLE #TEMP1
DROP TABLE #TEMP2
--DROP TABLE #TEMP3
--END
________________________________
THE ABOVE EXAMPLE FOR A TAG CLOUD
MY TAGS ARE AS FOLLOW
EVENTTAG
_________
ASP.NET, C#, VB.NET
WELCOME TO ASP.NET
ASP.NET BOOKS,C#.NET BOOKS
I WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NET
THE FOLLOWING CODE WILL GENERATE AND SPLIT IT AS
TAG
____
ASP.NET
c#
VB.NET
WELCOME TO ASP.NET
ASP.NET BOOKS
C#.NET BOOKS
HOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
