更新时间： 2014年4月12日,星期六
oracle支持正则表达式函数
- regexp_like
- regexp_substr
- regexp_instr
- regexp_replace
示例:
createtablename t1( varchar2(10));
insert into t1 values('AAAA');
insert into t1 values('BBB');
insert into t1 values('ABBB');
insert into t1 values('0AAAA');
insert into t1 values('ZAAA');
insert into t1 values('5AAA');
insert into t1 values('-AAA');
insert into t1 values('A-ZA');
insert into t1 values('8888');
commit;
select * from t1 where regexp_like(name,'[^0-9]') --匹配包含非数字字符串
select * from t1 where regexp_like(name,'[[:alpha:]]') --匹配包含字母字符串
select * from t1 where regexp_like(name,'^A-Z') --匹配A-Z开头字符串
select * from t1 where regexp_like(name,'[[:digit:]]') --匹配包含数字字符串
执行结果：
lbx@LUBX> select * from t1;
NAME
----------
AAAA
BBB
ABBB
0AAAA
ZAAA
5AAA
-AAA
A-ZA
8888
9 rows selected.
lbx@LUBX> select * from t1 where regexp_like(name,'[^0-9]');
NAME
----------
AAAA
BBB
ABBB
0AAAA
ZAAA
5AAA
-AAA
A-ZA
8 rows selected.
lbx@LUBX> select * from t1 where regexp_like(name,'[[:alpha:]]');
NAME
----------
AAAA
BBB
ABBB
0AAAA
ZAAA
5AAA
-AAA
A-ZA
8 rows selected.
lbx@LUBX> select * from t1 where regexp_like(name,'^A-Z');
NAME
----------
A-ZA
lbx@LUBX> select * from t1 where regexp_like(name,'[[:digit:]]');
NAME
----------
0AAAA
5AAA
8888
参考：