string search in store procedure

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)

string search in store procedure

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-26 : 11:38:14

I have a field in a database called categories with values separated by commas, example.
Arts,Education,Sports,Poetry
This field was saved into the table thourgh a list box selection element.
now in my application the user selects values from a list box and those values are compare to the database field categories. If one of those values selected in the search appears in the categories field that row is selected for display. example

eventID Categories
13 Arts,Sports,Poetry
14 Sports,Poetry,Lecture,Math
15 Arts,Religion,Math,Geography
16 Sports,Poetry,Lecture,History

In application user selects from list box:
Arts,Religion,History

so the records to return are:
13 15 16

currently my application is accepting one value from the list box and it works fine, i am using the '%like with wildcard%' option, but I need to pass and compare multiple values.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 11:40:51

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 to parse the string and use it to perform a LIKE comparison

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-26 : 11:58:58

can you give me a hint, i cannot get it to work, this is my store procedure code:

CREATE PROCEDURE dbo.sp_get_category
(
@multiCategory VARCHAR(1600)=NULL, @SQL VARCHAR(1600) =NULL
)
AS
BEGIN
IF(@multiCategory IS NULL)
BEGIN
SET @SQL='SELECT * FROM tblCategory
END

ELSE
SELECT * FROM tblcategory WHERE categoryName like '%' + @multiCategory + '%'

EXEC(@SQL)
END
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 18:46:45

sorry should be using fnParseList


select c.*
from   tblcategory c inner join dbo.fnParseList(' ', @multiCategory) m
on     c.categoryName like m.Data + '%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:55:09

[code]-- Prepare sample data
DECLARE @Sample TABLE (EventID INT, Categories VARCHAR(99))

INSERT @Sample
SELECT 13, 'Arts,Sports,Poetry' UNION ALL
SELECT 14, 'Sports,Poetry,Lecture,Math' UNION ALL
SELECT 15, 'Arts,Religion,Math,Geography' UNION ALL
SELECT 16, 'Sports,Poetry,Lecture,History'

DECLARE @UserPass VARCHAR(99)

SET @UserPass = 'Arts,Religion,History'

-- Show the expected output
SELECT s.EventID,
COUNT(*) AS Hits
FROM dbo.fnParseList(',', @UserPass) AS x
INNER JOIN @Sample AS s ON ',' + s.Categories + ',' LIKE '%,' + x.Data + ',%'
GROUP BY s.EventID
ORDER BY s.EventID[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:59:21

Or with the new SQL Server 2005 CROSS APPLY operator

-- Show the expected output
SELECT 		s.EventID,
		COUNT(*) AS Hits
FROM		@Sample AS s
CROSS APPLY	dbo.fnParseList(',', s.Categories) AS y
INNER JOIN	dbo.fnParseList(',', @UserPass) AS x ON x.Data = y.Data
GROUP BY	s.EventID
ORDER BY	s.EventID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources