CASE (Contains) rather than equal statement

Question

Is there a method to use contain rather than equal in case statement?

For example, I am checking a database table has an entry

lactulose, Lasix (furosemide), oxazepam, propranolol, rabeprazole, sertraline,  

Can I use

CASE When dbo.Table.Column = 'lactulose' Then 'BP Medication' ELSE '' END AS 'BP Medication' 

This did not work.

Thanks in advance

Answer 1

CASE WHEN ', ' + dbo.Table.Column +',' LIKE '%, lactulose,%'    THEN 'BP Medication' ELSE '' END AS [BP Medication] 

The leading ', ' and trailing ',' are added so that you can handle the match regardless of where it is in the string (first entry, last entry, or anywhere in between).

That said, why are you storing data you want to search on as a comma-separated string? This violates all kinds of forms and best practices. You should consider normalizing your schema.

In addition: don't use 'single quotes' as identifier delimiters; this syntax is deprecated. Use [square brackets] (preferred) or "double quotes" if you must. See "string literals as column aliases" here: http://msdn.microsoft.com/en-us/library/bb510662%28SQL.100%29.aspx

EDIT If you have multiple values, you can do this (you can't short-hand this with the other CASE syntax variant or by using something like IN()):

CASE    WHEN ', ' + dbo.Table.Column +',' LIKE '%, lactulose,%'    WHEN ', ' + dbo.Table.Column +',' LIKE '%, amlodipine,%'    THEN 'BP Medication' ELSE '' END AS [BP Medication] 

If you have more values, it might be worthwhile to use a split function, e.g.

USE tempdb; GO  CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX)) RETURNS TABLE AS    RETURN ( SELECT DISTINCT Item FROM        ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')          FROM ( SELECT [XML] = CONVERT(XML, '<i>'          + REPLACE(@List,',', '</i><i>') + '</i>').query('.')            ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y        WHERE Item IS NOT NULL    ); GO  CREATE TABLE dbo.[Table](ID INT, [Column] VARCHAR(255)); GO  INSERT dbo.[Table] VALUES (1,'lactulose, Lasix (furosemide), oxazepam, propranolol, rabeprazole, sertraline,'), (2,'lactulite, Lasix (furosemide), lactulose, propranolol, rabeprazole, sertraline,'), (3,'lactulite, Lasix (furosemide), oxazepam, propranolol, rabeprazole, sertraline,'), (4,'lactulite, Lasix (furosemide), lactulose, amlodipine, rabeprazole, sertraline,');  SELECT t.ID   FROM dbo.[Table] AS t   INNER JOIN dbo.SplitStrings('lactulose,amlodipine') AS s   ON ', ' + t.[Column] + ',' LIKE '%, ' + s.Item + ',%'   GROUP BY t.ID; GO 

Results:

ID ---- 1 2 4