SQL 2005我可以在case语句中使用关键字

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

5 个解决方案

#1

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

#2

Need to use a slightly different CASE syntax:

需要使用略有不同的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

#3

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

#4

You are using the "simple CASE expression" but you should be using the "searched CASE expression". See the documentation for some examples.

您正在使用“简单CASE表达式”，但您应该使用“搜索的CASE表达式”。有关示例，请参阅文档。

#5

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

#1