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


KH

Go to Top of Page

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
Go to Top of Page

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 + '%'



KH

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -