CODEWITHSUNDEEP
sql-server
kumar
kumar

Reputation: 209

SQL Server (2005) Query Help

My query is based on the following example

I have table say 'Table1'. Table1 has one row and one column. The name of the column is 'Column1'. 'Column1' is a text column (NVARCHAR). I have a comma separated keywords like 'key1,key2..keyn'. I want to search these keywords individually in the column1.

So in the where clause the query should be something like 

SELECT ... FROM Table1
WHERE Column1 LIKE '%key1%'
AND Column1 LIKE '%key2%'
AND Column1 LIKE '%keyn%'

I just want to know how to write a query in a simplified manner. Table is quite small and performance is not a main concern.

Just declare the keywords in a variable for the test case

DECLARE @Keywords NVARCHAR(MAX)
SET @Keywords = 'Key1,Key2,Key3'

A simple example will be helpful to me.

Upvotes: 1

Views: 39

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

This will be easier if you get these into table format. (Split Table Valued functions exist to do this to your delimited string - e.g. http://www.eggheadcafe.com/community/aspnet/13/10021854/fnsplit.aspx)

DECLARE @Keywords TABLE
(
COL NVARCHAR(100)
)

INSERT INTO @Keywords SELECT 'Key1' UNION ALL SELECT 'Key2' UNION ALL SELECT 'Key3'

SELECT ... 
FROM Table1  c 
JOIN @Keywords k ON c.Column1  LIKE '%' + k.COL + '%'
GROUP BY  ...
HAVING COUNT(*) = (select COUNT(*) FROM @Keywords)

Upvotes: 1

Related Questions