Oracle 中的正则函数

这个是函数中将会用到的模式串的介绍：

 

Oracle 中的正则函数一共有5个：

REGEXP_COUNT -- 统计子串出现的次数

REGEXP_INSTR -- 查找子串在母串中的位置

REGEXP_LIKE -- 模糊查询子串

REGEXP_REPLACE -- 替换

REGEXP_SUBSTR -- 截取

 

以下是官方关于这5个函数可能会用到的参数的介绍：

• source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

  
  

• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".

  
  

• position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

  
  

• occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.

  
  

• return_option lets you specify what Oracle should return in relation to the occurrence:

  • If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.

  • If you specify 1, then Oracle returns the position of the character following the occurrence.

• match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:

  • 'i' specifies case-insensitive matching.

  • 'c' specifies case-sensitive matching.

  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.

  • 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.

1. REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])

Sql代码  

1. -- 从'123123123123123'串中查找'123'子串的个数，起始查找位置为1  
2. select regexp_count('123123123123123', '123', 1, 'i') from dual;  
3. → 5  

 

2. REGEXP_INSTR(<source_string>, <pattern>

   [[, <start_position>][, <occurrence>][, <return_option>][, <match_parameter>][,<sub_expression>]])

Sql代码  

1. -- 从'500 Oracle Parkway, Redwood Shores, CA'串中查找 至少包含一个非空格字符的子串的位置  
2. -- 起始查找位置为1，查找匹配的第6个子串的位置  
3. select regexp_instr('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) from dual;  
4. → 37  
5.   
6. -- 从'500 Oracle Parkway, Redwood Shores, CA'串中查找  
7. -- 包含s或r或p字符，后面跟着6个字母的子串，不区分大小写('Parkway','Redwood'匹配)  
8. -- 起始查找位置为3，查找匹配的第2个子串('Redwood'匹配)  
9. -- 返回的位置是紧接着匹配子串后面的字符的位置('Redwood'后面空格的位置)  
10. select regexp_instr('500 Oracle Parkway, Redwood Shores, CA',  
11.                     '[s|r|p][[:alpha:]]{6}',  
12.                     3,  
13.                     2,  
14.                     1,  
15.                     'i')  
16.   from dual;  
17. → 28  

   

3. REGEXP_LIKE(<source_string>, <pattern>, <match_parameter>)

Sql代码  

1. -- 从指定列中查询出含有2个连续空格的字段  
2. select source_string  
3.   from (select 'hello, one space!' as source_string from dual  
4.         union all  
5.         select 'hello, two space! ' as source_string from dual  
6.         union all  
7.         select 'hello, two sequential space!  ' as source_string from dual)  
8.  where regexp_like(source_string, '[[:space:]]{2}');  
9.   
10. -- 从指定列中查询出first_name列以'Ste'开头，'en'结尾，中间包含字母'v'或'ph'的字段  
11. select first_name, last_name  
12.   from (select 'Steven' as first_name, 'King' as last_name from dual  
13.         union all  
14.         select 'Steven' as first_name, 'Markle' as last_name from dual  
15.         union all  
16.         select 'Stephen' as first_name, 'Stiles' as last_name from dual)  
17.  where regexp_like(first_name, '^Ste(v|ph)en$')  
18.  order by first_name, last_name;  

  

4. REGEXP_REPLACE(<source_string>, <pattern>, <replace_string>

   [, <position>[, <occurrence>[, <match_parameter>]]])

Sql代码  

1. -- 将字符串中连续的多个空格替换成一个空格  
2. select regexp_replace('500   Oracle     Parkway,    Redwood  Shores, CA',  
3.                       '( ){2,}',  
4.                       ' ')  
5.   from dual;  
6.   
7. -- 把'xxx.xxx.xxxx'格式的电话号码转换为'(xxx) xxx-xxxx'格式  
8. with T1 as(  
9.      select '515.123.4567' as phone_number from dual  
10.      union all  
11.      select '515.123.4568' as phone_number from dual  
12.      union all  
13.      select '(515) 123 4569' as phone_number from dual  
14.      union all  
15.      select '13207730591' as phone_number from dual)  
16. select regexp_replace(phone_number,  
17.                       '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',  
18.                       '(\1) \2-\3')  
19.   from T1;  
20.   
21. -- 给字段的每个字符后面加上空格  
22. with T2 as(  
23.      select 'Argentina' as country_name from dual  
24.      union all  
25.      select 'Australia' as country_name from dual  
26.      union all  
27.      select 'Belgium' as country_name from dual  
28.      union all  
29.      select 'Brazil' as country_name from dual  
30.      union all  
31.      select 'Canada' as country_name from dual)  
32. select regexp_replace(country_name,  
33.                       '(.)',  
34.                       '\1 ')  
35.   from T2;  

 

5. REGEXP_SUBSTR(<source_string>, <pattern>[, <position> [, <occurrence>[, <match_parameter>]]])

Sql代码  

1. -- 截取字符串中两个','之间的子串，子串只能开头和结尾含有','，中间至少要含有一个非','字符  
2. select regexp_substr('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,')  
3.   from dual;  
4.   
5. -- 截取字符串中包含 'http://' 子串，后面跟着3-4个 '字母/数字 0-1个.'，再跟着0-1个 '/' 的子串  
6. select regexp_substr('http://www.oracle.com/products',  
7.                      'http://([[:alnum:]]+\.?){3,4}/?')  
8.   from dual;  
9.     
10. with T3 as(select '1:3,4:6,8:10,3:4,7:6,11:12' as source_string from dual)  
11. -- 从source_string列中查找不含':'的子串，起始查找位置为1，返回第1个子串  
12. select regexp_substr(source_string,'[^:]+', 1, 1) from T3  
13. union all  
14. -- 从source_string列中查找不含':'的子串，起始查找位置为3，返回第2个子串  
15. select regexp_substr(source_string,'[^:]+', 3, 2) from T3;