SQL Server：将 CASE 与 LIKE 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23513869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: use CASE with LIKE
提问by Mike
I am pretty new to SQL and hope someone here can help me with this.
我对 SQL 很陌生，希望这里有人可以帮助我。
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 CASEwhen 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在检查与特定数字或值的匹配时使用，所以我不确定这个。有人可以告诉我以下内容是否有效和正确，或者让我知道如何正确编写（仅关于括号中的部分）？
(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'.
注：@selCountry 是一个国家的变量名，国家可以为空，一个国家或几个国家用逗号和空格分隔。基本上我只想检查国家是否包含@selCountry，如果是，将有效性设置为“国家”。
回答by dean
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.
虽然，根据您的原始问题，我会以不同的方式解决它，将 @selcountry 的内容拆分为表格形式并加入其中。
回答by Raging Bull
Add an ENDat last before alias name.
END最后在别名前添加一个。
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
回答by MelPogz
You can also do like this
你也可以这样做
select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %'
回答by Suwat Chaiwan
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
回答by podiluska
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.
您需要了解的有关 SQL（和关系数据库）的第一件事是，您不应该在单个字段中存储多个值。
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
