oracle sql - 选择带有多个“case when”的语句并检查包含文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38301031/
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
oracle sql - select statement with multiple "case when" and check for contains text
提问by megloff
I look for a working example where I can use mutliple when case statment wihch check to verify if a specific text is contained: e.g.
我寻找一个工作示例，当 case 语句检查以验证是否包含特定文本时，我可以使用 mutliple：例如
SELECT
ID,
NAME,
(SELECT
(Case when Contains(Descr,"Test") Then "contains Test"
when Contains(Descr, "Other") Then "contains Other"
Else "No Match" End) From DESCRIPTION
where item_id = id
) as "Match"
From Item
回答by MT0
In Oracle string literals need to be surrounded in single quotes.
在 Oracle 中，字符串文字需要用单引号括起来。
To find a sub-string match you can either use LIKE:
要查找子字符串匹配，您可以使用LIKE：
SELECT ID,
NAME,
CASE WHEN Descr LIKE '%Test%' THEN 'Contains Test'
WHEN Descr LIKE '%Other%' THEN 'Contains Other'
ELSE 'No Match'
END AS Match
FROM Item i
LEFT OUTER JOIN
Description d
ON i.id = d.item_id
or INSTR():
或INSTR()：
SELECT ID,
NAME,
CASE WHEN INSTR( Descr, 'Test' ) > 0 THEN 'Contains Test'
WHEN INSTR( Descr, 'Other' ) > 0 THEN 'Contains Other'
ELSE 'No Match'
END AS Match
FROM Item i
LEFT OUTER JOIN
Description d
ON i.id = d.item_id
or REGEXP_LIKE():
或REGEXP_LIKE()：
SELECT ID,
NAME,
CASE WHEN REGEXP_LIKE( Descr, 'Test' ) THEN 'Contains Test'
WHEN REGEXP_LIKE( Descr, 'Other' ) THEN 'Contains Other'
ELSE 'No Match'
END AS Match
FROM Item i
LEFT OUTER JOIN
Description d
ON i.id = d.item_id
回答by Aleksej
You probably need something like this:
你可能需要这样的东西：
with Item(id, name, descr) as
(
select 'id1', 'name1', 'description containing Test' from dual union all
select 'id2', 'name2', 'description containing Others' from dual union all
select 'id3', 'name3', 'description containing nothing interesting' from dual
)
SELECT
ID,
NAME,
descr,
case
when instr(Descr, 'Test') != 0 then 'contains Test'
when instr(Descr, 'Other')!= 0 then 'contains Other'
Else 'No Match'
End as "Match"
From Item
Using INSTRis only one of the possible solutions; you may use LIKE, regular expressions, etc and different ways of writing the same query; I believe this is plain enough to be quite self-explanatory.
使用INSTR只是可能的解决方案之一；您可以使用LIKE、正则表达式等以及编写相同查询的不同方式；我相信这很清楚，可以不言自明。
回答by Ersin Gülbahar
you can try this:
你可以试试这个：
SELECT
1,
2,
(SELECT
(
Case
when 'contains Test' like '%Test%'
Then 'contains Test'
when 'contains Test' like '%Other%'
Then 'contains Other'
Else 'No Match'
End
) From dual
where 1 = 1
) as "Match"
From dual
you can use likefunc
你可以使用likefunc
回答by lakshmi tatavarty
Use INSTR function instead of Contains
使用 INSTR 函数而不是包含
