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'.
The CASE statement always goes in the SELECT clause. CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN .
The SQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive.
The SQL Like is a logical operator that is used to determine whether a specific character string matches a specified pattern. It is commonly used in a Where clause to search for a specified pattern in a column. This operator can be useful in cases when we need to perform pattern matching instead of equal or not equal.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With