你的分享就是我们的动力 ---﹥

Oracle:regexp_replace正则表达式函数的使用

时间:2013-03-28 18:05来源:www.chengxuyuans.com 点击:
标签:regexp_replace regexp replace oracle 正则
函数的参数说明:
一共6个参数,分别是
1、待匹配的字符串
2、正则表达式
3、替换的字符
4、标识从第几个字符开始正则表达式匹配。(默认为1)
5、标识第几个匹配组。(默认为全部都替换掉)
6、取值范围:
i:大小写不敏感;
c:大小写敏感;(默认)
n:不匹配换行符号;
m:多行模式;
x:扩展模式,忽略正则表达式中的空白字符。

创建表,插入临时测试数据:
SQL> conn xzh/oracle
Connected.
SQL> create table regexp_replace_test (V varchar2(40), C char(40));

Table created.

SQL> insert into regexp_replace_test(v,c) values ('HelloWorld! X9x99X999x9999
  2  Happy2You!','HelloWorld! X9x99X999x9999
  3  Happy2You!');

1 row created.


查看数据:
SQL> select * from regexp_replace_test;

V
----------------------------------------
C
----------------------------------------
HelloWorld! X9x99X999x9999
Happy2You!
HelloWorld! X9x99X999x9999
Happy2You!


SQL> set lin 200
SQL> select * from regexp_replace_test;

V                                        C
---------------------------------------- ----------------------------------------
HelloWorld! X9x99X999x9999               HelloWorld! X9x99X999x9999
Happy2You!                               Happy2You!


替换x后面带数字的字符串为Love,区分大小写:
SQL> select regexp_replace(V,'x[0-9]+','Love',1,2,'i') V,regexp_replace(C,'x[0-9]+','Love',1,2,'i') C from regexp_replace_test;

V                                                  C
-------------------------------------------------- --------------------------------------------------
HelloWorld! X9LoveX999x9999                        HelloWorld! X9LoveX999x9999
Happy2You!                                         Happy2You!


替换x后面带数字的字符串为Love,不区分大小写:
SQL> select regexp_replace(V,'x[0-9]+','Love',1,2) V,regexp_replace(C,'x[0-9]+','Love',1,2) C from regexp_replace_test;

V                                                  C
-------------------------------------------------- --------------------------------------------------
HelloWorld! X9x99X999Love                          HelloWorld! X9x99X999Love
Happy2You!                                         Happy2You!

替换感叹号后面带空格的字符串为Love,注意varchar和char类型得到不同的结果:
SQL> select regexp_replace(V,'!\s','Love') V,regexp_replace(C,'!\s','Love') C from regexp_replace_test;

V                                                  C
-------------------------------------------------- --------------------------------------------------
HelloWorldLoveX9x99X999x9999                       HelloWorldLoveX9x99X999x9999
Happy2You!                                         Happy2YouLove

本文地址http://www.chengxuyuans.com/oracle/54955.html

其他频道: MySQL SQL Server oracle DB2 sybase