SQL 2005：我能在case语句中使用像关键字一样的内容吗？

Question

我需要一个允许部分匹配的case语句。我得到了一个语法错误，但是想知道是否可能有类似的解决方法。如果不行，我的备选方案是重写成游标...

SELECT CASE ColumnName
           WHEN 'value1' THEN 'answer1'
           WHEN 'value2' THEN 'answer2'
           WHEN LIKE '%TEST%' THEN 'answer3'
       END AS Answer
FROM TableName

- Paul

---

请勿使用游标！为了SQL之爱。 - Widor

你正在使用“简单的CASE表达式”，但是你应该使用“搜索CASE表达式”。请参阅文档。 - onedaywhen

Answer 1

试试这个

SELECT CASE 
           WHEN ColumnName = 'value1' THEN 'answer1'
           WHEN ColumnName = 'value2' THEN 'answer2'
           WHEN ColumnName LIKE '%TEST%' THEN 'answer3'
       END AS Answer
FROM TableName

你可以运行的示例

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

- SQLMenace

Answer 2

需要使用稍微不同的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

- Widor

Answer 3

或者，您可以嵌套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

- user359040

Answer 4

您正在使用“简单CASE表达式”，但应该使用“搜索CASE表达式”。请参阅文档以获取一些示例。

- onedaywhen

Answer 5

在 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

- jorgeblancov