oracle11Gregexp_like用法示例

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---2019.7.17 15:44 add

--字符内容含A或C
select * from (

select 1 id, 'ABC' s1 from dual union all
select 2 id,'AC' s1 from dual union all
select 3 id,'BC' s1 from dual union all
select 4 id,null s1 from dual union all
select 5 id,'' s1 from dual union all
select 6 id,'B' s1 from dual union all
select 7 id,'D' s1 from dual
) where regexp_like(nvl(s1,' '),'A|C')
;

ID S1
1 ABC
2 AC
3 BC

--字符内容不包含A 且不包含B
select * from (

ID S1
4
5
6 B
7 D

--字符内容含01或05 ,字符串两边加逗号再比较，防止匹配到011这种记录。
select * from (

select 1 id, '01,02,03' s1 from dual union all
select 2 id,'01,03' s1 from dual union all
select 3 id,'02,03' s1 from dual union all
select 4 id,null s1 from dual union all
select 5 id,'' s1 from dual union all
select 6 id,'011' s1 from dual union all
select 7 id,'05' s1 from dual union all
select 8 id,'012' s1 from dual union all
select 9 id,'06,013' s1 from dual
) where regexp_like(','||nvl(s1,' ')||',',',01,|,05,')
;

ID S1
1 01,02,03
2 01,03
7 05

--字符内容不包含01也包含05，空也要查出来
select * from (

ID S1
3 02,03
4
5
6 011
8 012
9 06,013

另外以下2个sql等价

sql1:

SELECT SUBSTR(UPPER(cardno),1,18) idcard18,lx.name as cartype_d,t.*
FROM TA t
LEFT JOIN TB LX ON cartype = LX.CODE AND
LX.dic_type='CARTYPE'
WHERE FILE_TIME = 20190716 --时间
AND NOT regexp_like(nvl(zt,' ') ,'B|C|D|E|H|J|K|L|M|O|P') --剔除车辆状态不符合要求的
AND cartype IN (select CODE from TB where dic_type='CARTYPE' AND NVL(FIELD1,'0') <> 1) --剔除摩托车等包括225种类型
order by idcard18

sql2:

SELECT SUBSTR(UPPER(cardno),1,18) idcard18,lx.name as cartype_d,d1.*,d2.*,t.*
FROM TA t
LEFT JOIN TB LX ON cartype = LX.CODE AND
LX.dic_type='CARTYPE'
LEFT JOIN (select listagg(CODE,'|') within group (order by code) dtype from TB where dic_type='CARTYPE' AND NVL(FIELD1,'0') = 1 ) d1
ON 1 = 1
LEFT JOIN (select listagg(CODE,'|') within group (order by code) dzt from TB where dic_type='ZT' AND NVL(FIELD1,'0') = 1 ) d2
ON 1 = 1
WHERE FILE_TIME = 20190716 --时间
AND NOT regexp_like(nvl(cartype,' ') ,d1.dtype)--剔除车辆状态不符合要求的
AND NOT regexp_like(nvl(zt,' ') ,d2.dzt)--剔除摩托车等包括225种类型
order by idcard18

其中
NOT regexp_like(nvl(zt,' ') ,'B|C|D|E|H|J|K|L|M|O|P')
等价于
(zt NOT LIKE '%B%' AND zt NOT LIKE '%C%' AND zt NOT LIKE '%D%' AND zt NOT LIKE '%E%'
AND zt NOT LIKE '%H%' AND zt NOT LIKE '%J%' AND zt NOT LIKE '%K%'
AND zt NOT LIKE '%L%' AND zt NOT LIKE '%M%' AND zt NOT LIKE '%O%' AND zt NOT LIKE '%P%' )

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--字符串从头到尾都是数字，即整个字符串是数字 regexp_like(字符串,'^[0-9]+$') 返回boolean,常用于where条件
select * from (
select 'a123' s1 from dual
union all select '123' s1 from dual
union all select null str from dual
union all select '1' s1 from dual
) where regexp_like(s1,'^[0-9]+$')
;
123
1

--返回日期格式如2018-05-16
select * from (
select '0123-45-15-8' s1 from dual
union all select '0981-789abcd' s1 from dual
union all select '2018-05-16' s1 from dual
union all select null str from dual
union all select 'abc012-1598' s1 from dual
) where regexp_like(s1,'^d{4}-d{2}-d{2}$')
;

2018-05-16

-----只取数字部分，字符替换掉
select id,regexp_replace(a1,'[^0-9]') from (
select 1 id,'1' a1 from dual union all
select 2 id,'a1b89' a1 from dual union all
select 3 id,' ' a1 from dual union all
select 4 id,null a1 from dual union all
select 5 id,'198c70205' a1 from dual
) ;
1 1
2 189
3
4
5 19870205

-----找出含有 xf 或 AB 的
select * from (
select 1 id, 'zfood' a1 from dual union all
select 2 id, 'xfood' a1 from dual union all
select 3 id, 'Z021' a1 from dual union all
select 4 id, 'cfood' a1 from dual union all
select 5 id, 'AB78' a1 from dual union all
select 6 id, 'x' a1 from dual
) where regexp_like(a1,'xf|AB')
2 xfood
4 AB78

-----找出含有 x 或 f 或 A 或 B 的
select * from (
select 1 id, 'zfood' a1 from dual union all
select 2 id, 'xfood' a1 from dual union all
select 3 id, 'Z021' a1 from dual union all
select 4 id, 'cfood' a1 from dual union all
select 5 id, 'AB78' a1 from dual union all
select 6 id, 'x' a1 from dual
) where regexp_like(a1,'[xf|AB]')
1 zfood
2 xfood
4 cfood
5 AB78
6 x

----逗号出现了几次
select s1,regexp_count(s1,',') a1 from (
select '01,06,07,09' s1 from dual union all
select '02' from dual union all
select null from dual union all
select ' ' from dual union all
select '06,07' from dual
);
01,06,07,09 3
02 0

0
06,07 1

--字符串中含有 0数字数字-数字数字数字的子串
select * from (
select '0123-456' s1 from dual
union all select '098-789abcd' s1 from dual
union all select null str from dual
union all select 'abc012-1598' s1 from dual
) where regexp_like(s1,'0dd-ddd')--等价于 regexp_like(s1,'0d{2}-d{3}') 只要求0加2个数字-3个数字，开头和最后
接数字或字符都不管
;
098-789abcd
abc012-1598

--字符串必现0开头再连续5个数字结尾
select * from (
select '0123-456' s1 from dual
union all select '098789' s1 from dual
union all select null str from dual
union all select 'abc0121598' s1 from dual
) where regexp_like(s1,'^0ddddd$')--等价于 regexp_like(s1,'^0d{5}$')
;
098789

--0开头2个字符结尾 w匹配字符或数字或下划线或汉字,但不匹配其他符号
select * from (
select '0我p' s1 from dual
union all select '01,' s1 from dual
union all select '012' s1 from dual
union all select '0a_' s1 from dual
union all select '0abb' s1 from dual
) where regexp_like(s1,'^0ww$')
;
0我p
012
0a_

--4个字符.txt结尾用于转义
select * from (
select 'word.txt' s1 from dual
union all select 'wordtxt' s1 from dual
union all select 'helloword.txt' s1 from dual
union all select '01ab.txt' s1 from dual
) where regexp_like(s1,'^w{4}.txt$')
;
word.txt
01ab.txt

--IP地址 xxx.xxx.xxx.xxx
select * from (
select '(012)-12345678' s1 from dual
union all select '1.12.2.1' s1 from dual
union all select '3.998.1.1' s1 from dual
) where regexp_like(s1,'^(d{1,3}.){3}d{1,3}$')
;
1.12.2.1
3.998.1.1

小结:

^放在[]外表示开头,放在[]里面表示非;

$表示结尾;

+表示一个或多个;

d和[0-9]都表示数字;

{4}表示4个;

[]表示里面的字符一个一个匹配;

|表示或者，没有[]的|表示多个字符一起匹配;