May 8, 2003 at 4:12 am
Can someone help me with a stored proc I want to create. I have a Field that I store Comma Separated Values into for a Keyword Search. For example "SQL Server, V2002, XP, Software, Development". I want to Search this field for a particular Value ie "Software". I thought about using LIKE to check it but that would query the full string as one. I am fairly new to SQL Server so be gentle. Thanks in Advance
May 8, 2003 at 6:53 am
It is true that you could use LIKE:
WHERE KeywordField LIKE '%Software%'
Or, you could use PATINDEX:
WHERE PATINDEX('%Software%', KeywordField) > 0
Or, you could go with a much more efficient, and elegant solution and normalize your table structure:
-- If you have a main table, Topic:
CREATE TABLE Topic (TopicID INT IDENTITY NOT NULL, Other fields...)
-- Then have another table:
CREATE TABLE TopicKeyword (TopicID INT, Keyword VARCHAR(100) NOT NULL)
That way, you have a single column storing the Keyword field, and with appropriate indexing, you'll be able to search many more records for your search items. One possible search could be:
SELECT TopicID FROM TopicKeyword
WHERE Keyword = 'Software'
The equality operator will always be faster and more efficient than the LIKE operator, especially when the LIKE operator acts on a value with a wildcard in front of the value being searched (i.e '%Software%')
Hope this gets you started. Good luck,
Jay
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply