数据库基本功之正则表达式

1. 四个主要正则表达式函数

1.REGEXP_LIKE :与LIKE的功能相似
2.REGEXP_INSTR :与INSTR的功能相似
3.REGEXP_SUBSTR :与SUBSTR的功能相似
4.REGEXP_REPLACE :与REPLACE的功能相似
 

用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同,但是它们使用POSIX 正则表达式代替了老的百分号(%)和通配符(_)字符.

 2. POSIX 正则表达式

POSIX 正则表达式由标准的元字符(metacharacters)构成: 

 3. 字符簇

[[:alpha:]]	# 任何字母.
[[:digit:]]	# 任何数字.
[[:alnum:]]	# 任何字母和数字.
[[:space:]]	# 任何白字符.
[[:upper:]]	# 任何大写字母.
[[:lower:]]	# 任何小写字母.
[[:punct:]]	# 任何标点符号.
[[:xdigit:]]# 任何16进制的数字,相当于[0-9a-fA-F].
# 各种操作符的运算优先级
\							# 转义符
(), (? :), (? =), [] 		# 圆括号和方括号
*, +,  ?, {n}, {n,}, {n,m} 	# 限定符
^, $, anymetacharacter 		# 位置和顺序
|
*/

4. Oracle REGEXP_LIKE介绍和案例

-- 创建表
create table test (id varchar2(4), value varchar2(10));
-- 数据插入
insert into test values ('1','1234560');
insert into test values ('2','1234560');
insert into test values ('3','1b3b560');
insert into test values ('4','abc');
insert into test values ('5','abcde');
insert into test values ('6','ADREasx');
insert into test values ('7','123 45');
insert into test values ('8','adc de');
insert into test values ('9','adc,.de');
insert into test values ('10','1B');
insert into test values ('10','abcbvbnb');
insert into test values ('11','11114560');
insert into test values ('11','11124560');



SQL> -- 查询value中以1开头60结束的记录并且长度是7位 
SQL> select * from test where value like '1____60';

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
3            1b3b560

SQL> select * from test where regexp_like (value,'1....60');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
3            1b3b560
11           11114560
11           11124560

SQL> -- 查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录.
SQL> -- 使用like就不是很好实现了.
SQL> select * from test where regexp_like(value,'1[0-9]{4}60');   -- [0-9]{4}之间不能有空格
  2  
SQL> -- [0-9]{4}之间不能有空格
SQL> select * from test where regexp_like(value,'1[0-9]{4}60');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
11           11114560
11           11124560

SQL> -- :不能有空格
SQL> select * from test where regexp_like (value,'1[[:digit:]]{4}60');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
11           11114560
11           11124560

SQL> -- :不能有空格
SQL> select * from test where not regexp_like (value,'^[[:digit:]] +$');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
3            1b3b560
4            abc
5            abcde
6            ADREasx
7            123 45
8            adc de
9            adc,.de
10           1B
10           abcbvbnb

ID           VALUE
------------ ------------------------------
11           11114560
11           11124560

13 rows selected.

SQL> -- 查询value中不包含任何数字的记录.
SQL> -- :不能有空格
SQL> select * from test where regexp_like (value,'^[^[:digit:]]+$');

ID           VALUE
------------ ------------------------------
4            abc
5            abcde
6            ADREasx
8            adc de
9            adc,.de
10           abcbvbnb

6 rows selected.

SQL> -- 查询以12或者1b开头的记录.不区分大小写.
SQL> select * from test where regexp_like(value,'^1[2b]','i');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
3            1b3b560
7            123 45
10           1B

SQL> -- 查询以12或者1b开头的记录.区分大小写.
SQL> select * from test where regexp_like(value,'^1[2B]');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
7            123 45
10           1B

SQL> -- 查询数据中包含空白的记录.
SQL> select * from test where regexp_like (value,'[[:space:]]');

ID           VALUE
------------ ------------------------------
7            123 45
8            adc de

SQL> -- 查询所有包含小写字母或者数字的记录.
SQL> select * from test where regexp_like(value,'^([a-z]+|[0-9]+)$');

ID           VALUE
------------ ------------------------------
1            1234560
2            1234560
4            abc
5            abcde
10           abcbvbnb
11           11114560
11           11124560

7 rows selected.

SQL> -- 查询任何包含标点符号的记录.
SQL> select * from test where regexp_like (value,'[[:punct:]]');

ID           VALUE
------------ ------------------------------
9            adc,.de

SQL> 

5. REGEXP_REPLACE(字符串替换函数)

-- REPLACE 函数是用另外一个值来替代串中的某个值.
-- 例如,可以用一个匹配数字来替代每次出现的字母.
-- REPLACE 的格式如下所示:
-- 原型:regexp_replace (x, pattern [, replace_string [, start [, occurence[match_option]]]])
-- 每个参数的意思分别是:
x 				待匹配的函数
pattern			正则表达式元字符构成的匹配模式
replace_string	替换字符串
start			开始位置
occurence		匹配次数
match_option	匹配参数,这里的匹配参数和regexp_like完全一样
-- 举例:

SQL> select regexp_replace ('hello everybody,051courses will be over soon, thanks.','b [[:alpha:]] {3}','one') from dual;

REGEXP_REPLACE('HELLOEVERYBODY,051COURSESWILLBEOVERSOON,THANKS.','B[[:ALPHA:]]{3
--------------------------------------------------------------------------------
hello everybody,051courses will be over soon, thanks.

SQL>