Writing a search query

[johnsmith180]

Hi
Just a quick question. A user enters "cars bikes"

Now I want to search for "cars" and "bikes" in my tables. I want to break the user search query and search for individual words. How can I do this

I can pass "cars bikes" to a stored procedure BUT how do I search for individual words.

regards

 

[jbenson001]

There are many threads and faqs on here regarding this issue. Look for the Split function. I know there are lots on this site.

 

[johnsmith180]

Thanks a lot.
After your post, I have searched the forum and found many posts regarding splitting a string.

However, those posts ask about splitting a string and inserting them into a table.

What I want to do is: split a string and search for each word in that string and return results back to the customer.

regards

 

[hmckillop]

If you find a post which shows you how to split a string into a table.
Declare a local table variable, split the string into it and then do a
SELECT * FROM MyTable WHERE Myfield in (Select FieldFromTempTable FROM @TempTable)

This should do it.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[johnsmith180]

hmckilop, currently, in my search query I am using LIKE '%'+@SearchText +'%'.

Would IN work in similar way???

regards

 

[hmckillop]

If you use in i.e.

SELECT * FROM MyTable WHERE [fieldname] in ('cars','bikes')

it will match all words in the in clause, i.e 'cars' and 'bikes' a seperate comparison e.g. returning all rows where fieldname = 'Cars' and all rows where fieldname ='bikes'

The problem in your case is that the string is all in one word, so you will need to split into seperate items in the first place, hence the SPLIT suggestion made by jbenson


"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Sorry, I just re-read the post, and I noticed an incorrect point.
IN would not be the same as LIKE, as it requires an exact match.
I am sure there is probably a simplier way of doing this but allowing for me being tired, here goes at least a way of doing it.

Use the SPLIT as previously suggested and put into a table (for this example we will call this table variable @SplitStringTable containing fields (myid int identity(1,1) , searchword varchar(50)).
You can then process the contents of the table to search like this.

Declare @sSQLString varchar(8000), @iMaxRow int, @iCurrentRow int
--initialise variables
select @sSQLString = '', @iMaxRow = max(myid) , @iCurrentRow = 1 
from @SplitStringTable
while @iCurrentRow <= @iMaxRow
BEGIN
	 SELECT @sSQLString = @sSQLString + 'SELECT * FROM MyTable Where Searchfield like ''%' + searchword + '%''' 
		From @SplitStringTable where myid = @iCurrentRow
	 SET @iCurrentRow = @iCurrentRow + 1
	IF @iCurrentRow <=@iMaxRow 
	BEGIN 
		SELECT @sSQLString = @sSQLString + ' UNION ' 
	END
END

exec( @sSQLString)

It should do what you want, probably not the most efficient, but should work

"I'm living so far beyond my income that we may almost be said to be living apart