SQL 中的 Case 语句使用 Like
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6740134/
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
Case Statement in SQL using Like
提问by SaiBand
Hello I have a SQL statement
你好，我有一个 SQL 语句
INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode
SELECT SystemTax.SystemTaxID,
EmpowerCalendarCode.CalendarCodeID
,CASE WHEN EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
WHEN EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
WHEN EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
ELSE 0
END
FROM Foundation.Common.SystemTax SystemTax, Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
WHERE SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode and SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType
Even though CalendarName has values like Quarterly (EOM) I still end up getting 0. Any ideas and suggestions!
即使 CalendarName 具有像 Quarterly (EOM) 这样的值，我最终还是得到 0。任何想法和建议！
回答by Taryn
For one, I would update your SQL to this so you are using a JOINon your SELECTstatement instead of placing this in a WHEREclause.
一方面，我会将您的 SQL 更新为此，以便您JOIN在SELECT语句中使用 a而不是将其放在WHERE子句中。
INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode
SELECT SystemTax.SystemTaxID,
EmpowerCalendarCode.CalendarCodeID
,CASE WHEN EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
WHEN EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
WHEN EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
ELSE 0
END
FROM Foundation.Common.SystemTax SystemTax
INNER JOIN Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
ON SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode
AND SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType
two, what happens if you remove the INSERT INTO?
二，如果你删除了会发生什么INSERT INTO？
回答by MelPogz
You can also do like this. I think this will work.
你也可以这样做。我认为这会奏效。
select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %'
回答by Chains
- Try ruling-out any null issues with ISNULL():
Try ruling-out any case-sensitivity issues with UPPER():
CASE WHEN upper(isnull(EmpowerCalendarCode.CalendarName, 'none')) LIKE '%MONTHLY%' THEN 3...
- 尝试用 ISNULL() 排除任何空问题：
尝试使用 UPPER() 排除任何区分大小写的问题：
CASE WHEN upper(isnull(EmpowerCalendarCode.CalendarName, 'none')) LIKE '%MONTHLY%' THEN 3 ...
