Multiple Keywords Search Query - My Approach

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Keywords Search Query - My Approach

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-24 : 05:23:47

Hi

Not being the SQL guru, I have spent alot of time to write a Search query that searches for multiple words in multiple columns. I wanted to return rows where ALL the search words are found.

So I took the following approach:

Search string = 'sensors monitors'

1 - first insert all the seacrh keywords into a table, so we get:

@Words
WordID   Word
1     sensors
2     monitors

2 - Now we want to search the Products table for all the words in @Words table. When a @Word.word matches any Products data, insert the Products.ProductID and @Words.WordID into a temp table @ProductsFound.

It will give us following data:

@ProductsFound
ProductID   Word
Prod1     1
Prod2     2
Prod3     1

3 - Finally I get all the @ProductsFound.ProductID where @ProductsFound.ProductID has ALL the WordIDs contained in @Words.WordID . So for the above @ProductsFound table, I would get "Prod1".

Perhaps, not the best solution, but does the job for me. Following is the SQL I am using:

CREATE procedure SearchSample

(@SearchString varchar(200), @MatchType int = 0)

as

declare @ProductsFound table (ProductID nvarchar(50), WordID int);

declare @Words table (Word varchar(200) not null, WordID int);

-- 1: split the search keywords
insert into @Words (WordID, Word) select WordID, Word from SplitSearchWords (@SearchString, @MatchType)

-- 2: Now search for products

Insert into @ProductsFound (ProductID, WordID)

select

(T.ProductID), W.WordID

from

Products T

LEFT OUTER JOIN O2_xREF o2 ON T.productid = o2.stk_ref

inner join

@Words W on ' ' + T.ProductName + ' ' like '%[^a-z]' + Word + '[^a-z]%'
OR ' ' + convert(Varchar(1000), T.ShortDescription ) + ' ' like '%[^a-z]' + Word + '[^a-z]%'

-------- search in the O2 -----
OR ' ' + o2.STK_REF + ' ' like '%[^a-z]' + Word + '[^a-z]%'
OR ' ' + o2.SENSOR_ + ' ' like '%[^a-z]' + Word + '[^a-z]%'

group by T.productID, W.WordID

-- 3: Finally get the results
-- Thanks to Madhivanan for the following code
select pf.productid from @ProductsFound pf
where pf.WordID in (select WordID from @Words)
group by pf.productID having count(productID)=(Select count(*) from @Words)
GO

Thanks for reading

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-24 : 08:15:06

I find it so strange that there is absolutely no information on the web regarding searching for multiple words in multiple columns.

I know you will suggest on using Full Text search, but I am unable to use it.

regards

Kristen
Test

22859 Posts

Posted - 2006-02-24 : 13:24:13

We do pretty much what you are doing. We have a RANK column in our @TempTable which we increment by the number of hits. More for a word found in the Product Name than the Product Description.

We also duplicate the words (from the SPLIT) and add pseudo plurals (or singulars). Basic as it is it gives us a pretty good hit ratio!


-- First get rid of unwanted punctuation, and make a comma-delimited-list (i.e. change SPACE to COMMA)
SELECT	@strFind = 
		LTRIM(RTRIM(
		REPLACE(
		REPLACE(
		REPLACE(
		REPLACE(@strFind, ';', ','),	-- Semi collon
			'+', ','),		-- Plus signs
			' ', ','),		-- Spaces
			',,', ',')))		-- Double Commas

-- Then "split" into @TempTable

-- Then deal with psuedo plurals:
INSERT INTO @tblDelimResults (Value)
SELECT	CASE WHEN Value IS NULL THEN NULL
		WHEN Value LIKE '%ies' THEN LEFT(Value, LEN(Value)-3) + 'Y'
		WHEN Value LIKE '%s' THEN LEFT(Value, LEN(Value)-1)
		WHEN Value LIKE '%y' THEN LEFT(Value, LEN(Value)-1) + 'IES'
		ELSE Value + 'S'
		END
FROM	@tblDelimResults
ORDER BY Value

-- Then make the search on various columns

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-24 : 13:51:52

http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

That must have been you who asked the question about searching multiple columns.

One thing you can do to simplify the code you've posted a little is to change:


 @Words W on ' ' + T.ProductName + ' ' like '%[^a-z]' + Word + '[^a-z]%'
OR ' ' + convert(Varchar(1000), T.ShortDescription ) + ' ' like '%[^a-z]' + Word + '[^a-z]%'

-------- search in the O2 -----
OR ' ' + o2.STK_REF + ' ' like '%[^a-z]' + Word + '[^a-z]%'
OR ' ' + o2.SENSOR_ + ' ' like '%[^a-z]' + Word + '[^a-z]%'


 @Words W on 
   ' ' + T.ProductName + ' ' + o2.STK_REF + ' ' + o2.SENSOR_ ' ' LIKE '%[^a-z]' + Word + '[^a-z]%'

since concatenating the columns eliminates the need for an OR clause makes it a little simplier.

Keep in mind that the code you posted doesn't follow what I wrote very well in my blog, and it will not return the same results that mine does, since it looks like you did quite a bit to overcomplicate it. If you followed my code more closely, I think you will see the results you are looking for. Otherwise, you'll need to explain what you mean by "its not working to its best".

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-27 : 10:40:32

quote:
Originally posted by jsmith8858

http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

That must have been you who asked the question about searching multiple columns.

You have preying eyes

jsmith8858, did you write that article?

As I am a beginner with SQL... what is the purpose of single quotes around a table column, e.g. ' ' + T.ProductName + ' ' ...

regards

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 11:01:53

to be sure that you get full words only, we search for each word surrounded by a non-alpha character. This excludes the first word and the last word in the column, so we pad the column with a ' ' on either side to be sure that the first word and last word are properly matched.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources