Monday, October 17, 2011

SQL query for multi keyword search

I recently came across a scenario where user enters multiple search keywords separated by delimitor character and needs to retrieve all rows from SQL Server database when certain column contains at least one of the words in the given list. I thought, this is common scenario with search screens. Thus I am posting queries I used to do this, if anyone likes to use this approach.

User enters list of search words like, 'word1,word2,word3'.

Here is function that returns a table with list of words as single column record set,

CREATE FUNCTION SplitStrings

(@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item varchar(200))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE varchar(8000)
SELECT @INDEX = 1
IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Item) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Then use above function in your query as shown in the sample query below,
 
SELECT * FROM table1, SplitStrings(@SearchWords,',')
WHERE CHARINDEX(item,SearchColumn)>0
 
You could use RTRIM/LTRIM functions on item if you need to get rid of leading or trailing spaces.
 
PS: Although this does work to get what we need, this may not be the best way to do this. There could be some other better ways also to do the same. Please do look for other ways if this is not optimal solution for your needs. 
 
HTH.