Oracle 案例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17304330/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 05:07:32 来源:igfitidea点击:
Oracle Case When Like
提问by Fenzl
I have the following code:
我有以下代码：
case when (a.je_source='Revaluation') then 'No_Location'
when d.user_name like ('SCHE%') then 'No_Location'
when d.user_name like ('C-FA%') then 'No_Location'
when d.user_name like ('C-AGO%') then 'No_Location'
when d.user_name like ('C-VD%') then 'No_Location'
when d.user_name like ('C-JL%') then 'No_Location'
else d.user_name
end as JE_User
Is there a way to make it cleaner? I tried the following and received a missing right parenthesis error.
有没有办法让它更干净？我尝试了以下操作，但收到了缺少右括号的错误。
case when (a.je_source='Revaluation') then 'No_Location'
when d.user_name like ('SCHE%', 'C-FA%') then 'No_Location'
else d.user_name
end as JE_User
回答by Nick Krasnov
As an option, you can use (oracle 10g and above) regexp_likecondition:
作为一种选择，您可以使用（oracle 10g 及更高版本）regexp_like条件：
-- sample of data
SQL> with t1(je_source, user_name) as(
2 select 'Revaluation1', 'SCHE123' from dual union all
3 select 'Revaluation2', 'C-FABCD' from dual union all
4 select 'Revaluation3', 'C-AGOABC' from dual union all
5 select 'Revaluation4', 'C-VD' from dual union all
6 select 'Revaluation5', 'C-JLABC' from dual union all
7 select 'Revaluation', 'ABCDE' from dual union all
8 select 'Revaluation6', 'FGHIJ' from dual
9 )
10 select je_source
11 , user_name
12 , case
13 when je_source = 'Revaluation'
14 then 'No_Location'
15 when regexp_like(user_name, '^SCHE\w*|^C-FA\w*|^C-AGO\w*|^C-VD\w*|^C-JL\w*', 'i')
16 then 'No_Location'
17 else user_name
18 end case
19 from t1
20 /
JE_SOURCE USER_NAME CASE
------------ --------- -----------
Revaluation1 SCHE123 No_Location
Revaluation2 C-FABCD No_Location
Revaluation3 C-AGOABC No_Location
Revaluation4 C-VD No_Location
Revaluation5 C-JLABC No_Location
Revaluation ABCDE No_Location
Revaluation6 FGHIJ FGHIJ
7 rows selected
