SQL 2005 我可以在 case 语句中使用关键字吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7687717/
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 2005 Can I use keyword like in a case statement
提问by Paul
I need a case statement that allows partial matches. I get a syntax error, but am wondering if anything similar to this is possible. If not, my secondary solution is to re-write as a cursor... Example:
我需要一个允许部分匹配的 case 语句。我收到一个语法错误，但我想知道是否有类似的可能。如果没有，我的次要解决方案是重写为游标...示例：
SELECT CASE ColumnName
WHEN 'value1' THEN 'answer1'
WHEN 'value2' THEN 'answer2'
WHEN LIKE '%TEST%' THEN 'answer3'
END AS Answer
FROM TableName
回答by SQLMenace
try this
尝试这个
SELECT CASE
WHEN ColumnName = 'value1' THEN 'answer1'
WHEN ColumnName = 'value2' THEN 'answer2'
WHEN ColumnName LIKE '%TEST%' THEN 'answer3'
END AS Answer
FROM TableName
example you can run
你可以运行的例子
SELECT name,CASE
WHEN Name = 'sysobjects' THEN 'answer1'
WHEN Name = 'syscols' THEN 'answer2'
WHEN Name LIKE '%p%' THEN 'answer3'
ELSE 'unknown'
END AS Answer
FROM sysobjects
回答by Widor
Need to use a slightly different CASEsyntax:
需要使用稍微不同的CASE语法：
SELECT CASE WHEN ColumnName LIKE 'value1' THEN 'answer1'
WHEN ColumnName LIKE 'value2' THEN 'answer2'
WHEN ColumnName LIKE '%TEST%' THEN 'answer3'
ELSE 'not like any of them' END [Answer]
FROM TableName
回答by Widor
Alternatively, you can nest CASE condition and CASE WHEN value clauses, like so:
或者，您可以嵌套 CASE 条件和 CASE WHEN 值子句，如下所示：
SELECT CASE ColumnName
WHEN 'value1' THEN 'answer1'
WHEN 'value2' THEN 'answer2'
ELSE CASE WHEN ColumnName LIKE '%TEST%' THEN 'answer3' END
END AS Answer
FROM TableName
回答by onedaywhen
You are using the "simple CASEexpression" but you should be using the "searched CASEexpression". See the documentationfor some examples.
您正在使用“简单CASE表达式”，但您应该使用“搜索CASE表达式”。有关一些示例，请参阅文档。
回答by jorgeblancov
In sql ORACLE 10
在 sql ORACLE 10 中
SELECT
CASE WHEN instr(ColumnName, 'value1')>0 THEN 'answer1'
WHEN instr(ColumnName,'value2')>0 THEN 'answer2'
WHEN instr(ColumnName,'TEST')>0 THEN 'answer3'
ELSE 'not like any of them'
END
FROM TableName
