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 '%'+@selCountry+'%' 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 related to
sql
sql-server
stored-procedures
case
sql-like
This is the syntax you need:
CASE WHEN countries LIKE '%'+@selCountry+'%' 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.
One of the first things you need to learn about SQL (and relational databases) is that you shouldn't store multiple values in a single field.
You should create another table and store one value per row.
This will make your querying easier, and your database structure better.
select
case when exists (select countryname from itemcountries where yourtable.id=itemcountries.id and countryname = @country) then 'national' else 'regional' end
from yourtable
SELECT Lname, Cods, CASE WHEN Lname LIKE '% HN%' THEN SUBSTRING(Lname,
CHARINDEX(' ', Lname) - 50, 50) WHEN Lname LIKE 'HN%' THEN Lname ELSE
Lname END AS LnameTrue FROM dbo.____Fname_Lname
Add an END
at last before alias name.
CASE WHEN countries LIKE '%'+@selCountry+'%'
THEN 'national' ELSE 'regional'
END AS validity
For example:
SELECT CASE WHEN countries LIKE '%'+@selCountry+'%'
THEN 'national' ELSE 'regional'
END AS validity
FROM TableName
You can also do like this
select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %'
Source: Stackoverflow.com