SQL Server: use CASE with LIKE

Question

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'.

Dethrone

Answer 1

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.

Alcazar

Answer 2

Add an END before alias name.

CASE WHEN countries LIKE '%'+@selCountry+'%' THEN 'national' 
     ELSE 'regional' 
     END 
AS validity

Sparry

Answer 3

You can also do like this

select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end  +  ' %'

Meingoldas

Answer 4

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

Sarthe

Answer 5

Maybe can try this MSSQL

select 
(case 
when COLNAME like '%case1%' then '1'
when COLNAME like '%case2%' then '2'
when COLNAME like '%case3%' then '3'
else 'Other'
end) as 'test'
from TABLENAME

This approach has advantages when dealing with complex situations. Of course other programming languages ​​can also be used.

select 
(case 
when COLNAME1 = 'condition1' then '1'
when COLNAME2 = 'condition2' and isnull(COLNAME3,'0')='1' then '2'
else '99'
end) as 'test'
from TABLENAME

Follow this rule you can get a sequence.

OK~I found my misunderstanding. I just thinks.

if(){
}else{
}

It is two rule. What if I have many rules?

if(){
}else if (){
}else if (){
}else{
}

Thank you Jeremy Caney.

Emlin

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach? – Dittography

Answer 6

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

Standley

"You're doing it wrong" is not a helpful answer. A useful comment, perhaps. – Semiporcelain

@Semiporcelain When someone, like the OP, is "pretty new to SQL", I would argue that "you're doing it wrong" is the most helpful answer. – Standley

the person querying the database is not always the person who designed the database... – Foveola