seperating words in a string passed to a stored pr

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)

seperating words in a string passed to a stored pr

Author

Topic

MM
Starting Member

2 Posts

Posted - 2002-04-12 : 19:19:05

Hi,

I am writting a stored procedure, that takes a string as a input parameter, and returns all records containing the words in that string.
The parameter @keywords can hold any number of words, which are seperated by commas (ex: @keywords = 'test1,test2,test3')
I would like to search the database for these words.
The table looks like this:

ID Item Keyword

1 5 test1
2 6 test1
3 7 test3
4 7 test2

So, if @keywords = 'test1,test3'
I need to get back records:
1 5
2 6
3 7

This is what I have so far...

CREATE PROCEDURE sp_GetItems(
@keywords varchar(8000)
)
AS

SELECT ID, Item
from Keywords
WHERE Keyword LIKE '%@keywords%'
GO

This obviously does not work. I know I need to somehow seperate the individual words in @keywords, but I am not sure how to do this.

Any ideas, examples, hints would be really appreciated!

Thanks so much

nabeel
Starting Member

15 Posts

Posted - 2002-04-12 : 20:10:30

Hi...

Is there any way you can format that string before you pass it to teh stored procedure? If you can manipulate the string to go from:

test1, test3

to

'test1', 'test3'

then the following would get you the results you are looking for:

SELECT ID, item FROM keywords WHERE keyword IN ('test1', 'test3')

You can just use that SELECT statement, you don't even have to write a stored procedure.

quote:

Hi,

I am writting a stored procedure, that takes a string as a input parameter, and returns all records containing the words in that string.
The parameter @keywords can hold any number of words, which are seperated by commas (ex: @keywords = 'test1,test2,test3')
I would like to search the database for these words.
The table looks like this:

ID Item Keyword

1 5 test1
2 6 test1
3 7 test3
4 7 test2

So, if @keywords = 'test1,test3'
I need to get back records:
1 5
2 6
3 7

This is what I have so far...

CREATE PROCEDURE sp_GetItems(
@keywords varchar(8000)
)
AS

SELECT ID, Item
from Keywords
WHERE Keyword LIKE '%@keywords%'
GO

This obviously does not work. I know I need to somehow seperate the individual words in @keywords, but I am not sure how to do this.

Any ideas, examples, hints would be really appreciated!

Thanks so much

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 21:12:12

There are a number of articles on CSVs that will help you parse them:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Once they're parsed, you can either INSERT them into a temp table and JOIN that to the Keywords table, or build the IN clause using dynamic SQL. This is detailed in these articles:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources