comma separated list search - SQL Server Forums

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

Transact-SQL (2000)

comma separated list search

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-08 : 16:39:03

I have the following SQL. What I would like to do is allow people to enter a comma separated list as the @Keywords param. Like "test,word", and if either test OR work are found, then display the results. How can I modify the SQL below to accomplish that? Obviously, the LIKE only gets me so far. Thanks

SELECT KnowledgeBaseID, URL, ContentType.ContentTypeID, ContentTypeDisplayName , KnowledgeBase.Keywords
FROM KnowledgeBase INNER JOIN
ContentType ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
WHERE KnowledgeBase.Keywords LIKE '%' + @Keywords + '%'
ORDER BY URL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 16:41:55

try it the other way around:

...
Where ','+@Keywords+',' LIKE '%,'+KnowledgeBase.Keywords+',%'
...

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-08 : 16:49:57

quote:
Originally posted by Seventhnight

try it the other way around:

...
Where ','+@Keywords+',' LIKE '%,'+KnowledgeBase.Keywords+',%'
...

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

That only works if you type in all tyhe Keywords, like "test,word"
If you type in "test" you get nothing back....Any other thoughts? Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 17:00:41

Is the knowledgeBase.Keywords field a list as well??

I think I would use the below function and split your search criteria...


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 

	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

Then the query would look more like:


SELECT KnowledgeBaseID, URL, ContentType.ContentTypeID, ContentTypeDisplayName , KnowledgeBase.Keywords
FROM KnowledgeBase 
INNER JOIN ContentType 
ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
Inner Join Select keyWord = data From dbo.Split(@keyWords,',')) Criteria
On ','+KnowledgeBase.Keywords+',' like '%,'+Criteria.keyWord+',%'
ORDER BY URL

you can also read about a similar instance here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40941

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-08 : 17:15:36

KnowledgeBase.Keywords is field that holds multiple values. So, the data structure look like
ID | URL | Keywords
1 | test.htm | test,word

So, when I search on either "test" or "word", I want to get that row back. or if I search on both "test,word". If I search on "t" I want to get that row back. The below method, even with that function, only returned results if I search on "test,word"

Thank you for all the help. I really appreciate it.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 17:28:04

Try this example:


Declare @myTable table (url varchar(1000), keywords varchar(100))
Insert Into @myTable
Select 'http://www.sqlteam.com', 'SQL,SQL Server,Forums'
Union Select 'http://www.seventhnight.com', 'Corey,Aldebol,Seventhnight,SQL,Photos,Trees'

Declare @criteria varchar(100)
Set @criteria = 'SQL'

Select * From @myTable A Inner Join (Select keyword=Data From dbo.Split(@criteria,',')) B On ','+A.keywords+',' like '%,'+B.Keyword+',%'

Set @criteria = 'Seventhnight'

Select * From @myTable A Inner Join (Select keyword=Data From dbo.Split(@criteria,',')) B On ','+A.keywords+',' like '%,'+B.Keyword+',%'

Set @criteria = 'Forums,Trees'

Select * From @myTable A Inner Join (Select keyword=Data From dbo.Split(@criteria,',')) B On ','+A.keywords+',' like '%,'+B.Keyword+',%'

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-08 : 17:43:43

It obviously works in this example...but for some reason I cannot get it to work with mine. My keywords are "pdf,transfusion" If I type in pdf, works fine...If it type in "pdf,transfusion", works fine. If I use "transfusion"...doesn't return records...Guess I will debug more. Thanks for all the help.

SELECT KnowledgeBaseID, URL, ContentType.ContentTypeID, ContentTypeDisplayName , KnowledgeBase.Keywords
FROM KnowledgeBase
INNER JOIN ContentType
ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
INNER JOIN (Select keyword=Data From dbo.Split(@Keywords,',')) B On ','+KnowledgeBase.Keywords+',' like '%,'+B.Keyword+',%'
ORDER BY URL;

HC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 17:46:09

make sure you don't have any trailing spaces or anything like that...

Good Luck!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-09 : 09:16:18

quote:
Originally posted by Seventhnight

make sure you don't have any trailing spaces or anything like that...

Good Luck!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

ugh! Thats what it was...spaces in the Knowledgebase.Keywords field!! The field has data like "pdf, transfusion" When I changed it to "pdf,transfusion" and put a DISTINCT in the select, it works great. Thanks alot

HC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-09 : 14:04:28

very cool!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-09 : 15:11:26

Seventhnight,
Thanks for all your help. I have one more question. I know this can't be too hard, but I am still trying to work through what the SPLIT function is doing, so, I figure, I'll ask, what the hey...

Is there anyway I can modifiy the query so that if the user just inputs "t", then they will get results that include "test" or "time"...similar to how a standard LIKE statement would work? Thanks for any advice, again, I really appreciate it

SELECT DISTINCT KnowledgeBaseID, URL, ContentType.ContentTypeID, ContentTypeDisplayName , KnowledgeBase.Keywords
FROM KnowledgeBase
INNER JOIN ContentType
ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
INNER JOIN (Select keyword=Data From dbo.Split(@Keywords,',')) B On ','+KnowledgeBase.Keywords+',' like '%,'+B.Keyword+',%'
ORDER BY URL;

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-09 : 15:26:36

sure... but the change wouldn't be in the split function. It would be in your query.

The split function changes 'corey,seventhnight,forums' into a recordset like:

keyword
--------
corey
seventhnight
forums

to search for 't' and get test you would just add one more % to the query:


SELECT DISTINCT KnowledgeBaseID, URL, ContentType.ContentTypeID, ContentTypeDisplayName , KnowledgeBase.Keywords
FROM KnowledgeBase 
INNER JOIN ContentType 
ON KnowledgeBase.ContentTypeID = ContentType.ContentTypeID
INNER JOIN (Select keyword=Data From dbo.Split(@Keywords,',')) B
On ','+KnowledgeBase.Keywords+',' like '%,'+B.Keyword+'%,%'
ORDER BY URL;

Or you could allow the user to input a wildcard. Like they input 's%' or 't%'.

Whatever is more appropriate.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-09 : 15:47:01

Yes, I knew it would be in the query..I just meant I was trying to get my mind around that function

Thanks, that worked great.

HC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources