SQL Server: use CASE with LIKE


I am pretty new to SQL and hope someone here can help me with this.

I have a stored procedure where I would like to pass a different value depending on whether a column contains a certain country or not.

So far I only used CASE when checking for the match with a specific number or value so I am not sure about this one. Can someone tell me if the following is valid and correct or let me know how to write this properly (just regarding the part in brackets) ?

(CASE countries
     WHEN LIKE '%'[email protected]+'%' THEN 'national'
     ELSE 'regional') AS validity

Notes: @selCountry is the variable name of a country, countries can either be empty, one country or several countries separated with comma and space. Basically I just want to check if countries contains @selCountry and if yes, set validity to 'national'.

This question is tagged with sql sql-server stored-procedures case sql-like

~ Asked on 2014-05-07 09:26:32

The Best Answer is


This is the syntax you need:

CASE WHEN countries LIKE '%'[email protected]+'%' THEN 'national' ELSE 'regional' END

Although, as per your original problem, I'd solve it differently, splitting the content of @selcountry int a table form and joining to it.

~ Answered on 2014-05-07 09:28:45


Add an END at last before alias name.

CASE WHEN countries LIKE '%'[email protected]+'%' 
     THEN 'national' ELSE 'regional' 
END AS validity

For example:

SELECT CASE WHEN countries LIKE '%'[email protected]+'%' 
       THEN 'national' ELSE 'regional' 
       END AS validity
FROM TableName

~ Answered on 2014-05-07 09:29:04

Most Viewed Questions: