Oracle 正则表达式

最新推荐文章于 2021-04-11 08:44:09 发布
最新推荐文章于 2021-04-11 08:44:09 发布
阅读量7k 收藏 9
点赞数 1
文章标签： oracle 正则表达式 character newline sql collation

       Oracle 10g数据库内建了符合IEEE POSIX (Portable Operating System for Unix)标准的正则表达式。熟练使用正则表达式，可以写出简洁，强大的SQL语句。 

       正则表达式有几个优点优于常见的LIKE操作符和INSTR、SUBSTR及REPLACE 函数的。这些传统的SQL 函数不便于进行模式匹配。只有LIKE 操作符通过使用%和_字符匹配，但LIKE不支持表达式的重复、复杂的更替、字符范围、字符列表和POSIX 字符类等等。


元字符（Meta Character）：

Sql代码  
  1. ^            使表达式定位至一行的开头  
  2. $            使表达式定位至一行的末尾  
  3. *            匹配 0 次或更多次  
  4. ?            匹配 0 次或 1 次  
  5. +            匹配 1 次或更多次  
  6. {m}          正好匹配 m 次  
  7. {m,}         至少匹配 m 次  
  8. {m, n}       至少匹配 m 次但不超过 n 次  
  9. [:alpha:]    字母字符  
  10. [:lower:]    小写字母字符  
  11. [:upper:]    大写字母字符  
  12. [:digit:]    数字  
  13. [:alnum:]    字母数字字符  
  14. [:space:]    空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符[:punct:]    标点字符  
  15. [:cntrl:]    控制字符（禁止打印）  
  16. [:print:]    可打印字符 | 分隔替换选项，通常与分组操作符 () 一起使用  
  17. ( )          将子表达式分组为一个替换单元、量词单元或后向引用单元  
  18. [char]       字符列表  

 

Oracle 10g提供了四个regexp function: REGEXP_LIKE, REGEXP_REPLACE,REGEXP_INSTR,REGEXP_SUBSTR。

Sql代码  
  1. REGEXP_LIKE：比较一个字符串是否与正则表达式匹配  
  2. (srcstr, pattern [, match_option])  
  3.   
  4. REGEXP_INSTR：在字符串中查找正则表达式，并且返回匹配的位置  
  5. (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])  
  6.   
  7. REGEXP_SUBSTR：返回与正则表达式匹配的子字符串  
  8. (srcstr, pattern [, position [, occurrence [, match_option]]])   
  9.   
  10. REGEXP_REPLACE：搜索并且替换匹配的正则表达式  
  11. (srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])  
 

其中各参数的含义为:

Sql代码  
  1. srcstr:        被查找的字符数据。   
  2. pattern:       正则表达式。  
  3. occurrence:    出现的次数。默认为1。  
  4. position:      开始位置  
  5. return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。  
  6. replacestr:    用来替换匹配模式的字符串。  
  7. match_option:  匹配方式选项。缺省为c。  
  8.                c：case sensitive  
  9.                I：case insensitive  
  10.                n：(.)匹配任何字符（包括newline)  
  11.                m：字符串存在换行的时候被作为多行处理  
 

下面通过一些具体的例子来说明如何使用这四个函数。首先创建一个测试数据表，

Sql代码  
  1. SQL> create table person (  
  2.   2  first_name varchar(20),  
  3.   3  last_name varchar(20),  
  4.   4  email varchar(100),  
  5.   5  zip varchar(6));  
  6.   
  7. Table created.  
  8.   
  9. SQL> insert into person values ('Steven', 'Chen', 'steven@hp.com', '123456');  
  10. 1 row created.  
  11.   
  12. SQL> insert into person values ('James', 'Li', 'jamesli@sun.com' || chr(10) || 'lijames@oracle.com', '1b3d5f');  
  13. 1 row created.  
  14.   
  15. SQL> commit;  
  16. Commit complete.  
  17.   
  18. SQL> select * from person;  
  19.   
  20. FIRST_NAME LAST_NAME  EMAIL                ZIP  
  21. ---------- ---------- -------------------- ------  
  22. Steven     Chen       steven@hp.com        123456  
  23. James      Li         jamesli@sun.com      1b3d5f  
  24.                       lijames@oracle.com  

 

1。REGEXP_LIKE

Sql代码  
  1. SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]');  
  2. INVALID_ZIP  
  3. --------------------  
  4. 1b3d5f  
  5.   
  6. SQL> select first_name from person where regexp_like(first_name, '^S.*n$');  
  7. FIRST_NAME  
  8. ----------  
  9. Steven  
  10.   
  11. SQL> select first_name from person where regexp_like(first_name, '^s.*n$');  
  12. no rows selected  
  13.   
  14. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c');  
  15. no rows selected  
  16.   
  17. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i');  
  18. FIRST_NAME  
  19. ----------  
  20. Steven  
  21.   
  22. SQL> select email from person where regexp_like(email, '^james.*com$');  
  23. no rows selected  
  24.   
  25. SQL> select email from person where regexp_like(email, '^james.*com$', 'n');  
  26. EMAIL  
  27. --------------------  
  28. jamesli@sun.com  
  29. lijames@oracle.com  
  30.   
  31. SQL> select email from person where regexp_like(email, '^li.*com$');  
  32. no rows selected  
  33.   
  34. SQL> select email from person where regexp_like(email, '^li.*com$', 'm');  
  35. EMAIL  
  36. --------------------  
  37. jamesli@sun.com  
  38. lijames@oracle.com  

 

注意上面分别测试了不同的match_option对结果的影响。

 

2。REGEXP_INSTR

Sql代码  
  1. 查找zip中第一个非数字字符的位置  
  2. SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person;  
  3.   POSITION  
  4. ----------  
  5.          0  
  6.          2  
  7.   
  8. 从第三个字符开始，查找zip中第二个非数字字符的位置  
  9. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person;  
  10.   POSITION  
  11. ----------  
  12.          0  
  13.          6  
  14.   
  15. 从第三个字符开始，查找zip中第二个非数字字符的下一个字符位置  
  16. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person;  
  17.   POSITION  
  18. ----------  
  19.          0  
  20.          7  

 

3。REGEXP_SUBSTR

Sql代码  
  1. SQL> select regexp_substr(zip, '[^[:digit:]]') as zip from person;  
  2. ZIP  
  3. ------------------  
  4. b  
  5.   
  6. SQL> select regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person;  
  7. ZIP  
  8. ------------  
  9. f  
 

4。REGEXP_REPLACE

Sql代码  
  1. 把zip中所有非数字字符替换为0  
  2. SQL> update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')  
  3.   2  where regexp_like(zip, '[^[:digit:]]');  
  4. 1 row updated.  
  5.   
  6. SQL> select zip from person;  
  7. ZIP  
  8. ------  
  9. 123456  
  10. 103050  
 

后向引用（backreference）：

 

后向引用是一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用。缓冲区从左至右进行编号，并利用 \digit 符号进行访问。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。

Sql代码  
  1. SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;  
  2.   
  3. REVERSED_NAME  
  4. --------------------  
  5. Chen, Steven  
 

在DDL中也可以正则表达式，比如Constraint, index, view

Sql代码  
  1. SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));  
  2.   
  3. SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]')); 

ORACLE终于在10G中提供了对正则表达式的支持，以前那些需要通过LIKE来进行的复杂的匹配就可以通过使用正则表达式更简单的实现。

Oracle10g正则表达式提高了SQL灵活性。有效的解决了数据有效性，重复词的辨认,无关的空白检测，或者分解多个正则组成的字符串等问题。

Oracle10g支持正则表达式的四个新函数分别,它们使用POSIX正则表达式代替了老的百分号（%）和通配符（_）字符。

1,REGEXP_LIKE：与LIKE的功能相似

2,REGEXP_INSTR：与INSTR的功能相似：返回字符的个数

3，REGEXP_SUBSTR：与SUBSTR的功能相似：截取字符串

4，REGEXP_REPLACE：与REPLACE的功能相似

在新的函数中使用正则表达式来代替通配符‘%’和‘_’。

正则表达式由标准的元字符（metacharacters）所构成：

'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'n'或'r'。

'.'匹配除换行符n之外的任何单字符。

'?'匹配前面的子表达式零次或一次。

'+'匹配前面的子表达式一次或多次。

'*'匹配前面的子表达式零次或多次。

'|'指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

'()'标记一个子表达式的开始和结束位置。

'[]'标记一个中括号表达式。

'{m,n}'一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。

num匹配num，其中num是一个正整数。对所获取的匹配的引用。

字符簇：

[[:alpha:]]任何字母。

[[:digit:]]任何数字。

[[:alnum:]]任何字母和数字。

[[:space:]]任何白字符。

[[:upper:]]任何大写字母。

[[:lower:]]任何小写字母。

[[:punct:]]任何标点符号。

[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级

1.转义符

2.(),(?:),(?=),[]圆括号和方括号

3.*,+,?,{n},{n,},{n,m}限定符

4.^,$,anymetacharacter位置和顺序

4.|“或”操作

示例：

一、REGEXP_LIKE

/*Formattedon2009/11/1119:03(FormatterPlusv4.8.8)*/

SELECT*

FROMscott.emp

WHEREREGEXP_LIKE(ename,'^a{1,3}');

SELECT*

FROMscott.emp

WHEREREGEXP_LIKE(ename,'a{1,2}');

SELECT*

FROMscott.emp

WHEREREGEXP_LIKE(ename,'^a.*e$');

SELECT*

FROMscott.emp

WHEREREGEXP_LIKE(ename,'^[[:lower:]]|[[:digit:]]');

SELECT*

FROMscott.emp

WHEREREGEXP_LIKE(ename,'^[[:lower:]]');

SELECTsal

FROMscott.emp

WHEREREGEXP_LIKE(sal,'[^[:digit:]]');

SELECTsal

FROMscott.emp

WHEREREGEXP_LIKE(sal,'^[^[:digit:]]');

二、REGEXP_INSTR

/*Formattedon2009/11/1119:03(FormatterPlusv4.8.8)*/

SELECTREGEXP_INSTR(ename,'[[:digit:]]$')

FROMscott.emp;

SELECTREGEXP_INSTR(mc,'[[:digit:]]+$')

FROMscott.emp;

SELECTREGEXP_INSTR('Thepriceis$400.','$[[:digit:]]+')

FROMDUAL;

SELECTREGEXP_INSTR('onetwothree','[^[[:lower:]]]')

FROMDUAL;

SELECTREGEXP_INSTR(',,,,,','[^,]*')

FROMDUAL;

SELECTREGEXP_INSTR(',,,,,','[^,]')

FROMDUAL;

三、REGEXP_SUBSTR

/*Formattedon2009/11/1119:04(FormatterPlusv4.8.8)*/

SELECTREGEXP_SUBSTR(ename,'[a-z]+')

FROMscott.emp;

SELECTREGEXP_SUBSTR(sal,'[0-9]+')

FROMscott.emp;

SELECTREGEXP_SUBSTR('aababcde','^a.*b')

FROMDUAL;

四、REGEXP_REPLACE

/*Formattedon2009/11/1119:06(FormatterPlusv4.8.8)*/

SELECTREGEXP_REPLACE('JoeSmith','(){2,}',',')ASrx_replace

FROMDUAL;

SELECTREGEXP_REPLACE('aabbcc','(.*)(.*)(.*)','3,2,1')

FROM DUAL ;


Oracle SQL 语句中关于正则表达式的函数有
REGEXP_LIKE(匹配)
REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)

如 手机号码的表达式： ^[1]{1}[35]{1}[[:digit:]]{9}$

查询客户信息表(tKHXX)中有手机号码(SJHM)的可以这样查询

源码 复制 打印
  1. SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' )  
Sql代码
  1. SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' )  

针对这个表达式解释一下
^ 表示开始
$ 表示结束
[]内部为匹配范围
{}里的内容表时个数

手机号码的特点是以 1开头接着是3或5再加9位的数字 所以这么理解
1开头 表达式为 ^[1]{1} 意为 开始1位里包含1
3或5 表达式为 [35]{1}
9位数字结束 为: [[:digit:]]{9}$ 这里[:digit:]为特殊写法，代表为数字 再加个结束符$

用则表达式很简单，更高效
下面列一些参考，来自网络 :)

Anchoring Characters
^ Anchoring Characters
$ Anchor the expression to the end of a line

Equivalence Classes
= =
Oracle supports the equivalence classes through the POSIX '[==]' syntax. A base letter and all of its accented versions constitute an equivalence class. For example, the equivalence class '[=a=]' matches ?and ? The equivalence classes are valid only inside the bracketed expression
Match Options
c Case sensitive matching
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character
Posix Characters

[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
Quantifier Characters

* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters

下面是个测试例子及环境

测试表

源码 复制 打印
  1. CREATE TABLE test (   
  2. testcol VARCHAR2(50));   
  3.   
  4. INSERT INTO test VALUES ('abcde');   
  1. INSERT INTO test VALUES ('12345');   
  2. INSERT INTO test VALUES ('1a4A5');   
  3. INSERT INTO test VALUES ('12a45');   
  4. INSERT INTO test VALUES ('12aBC');   
  5. INSERT INTO test VALUES ('12abc');   
  6. INSERT INTO test VALUES ('12ab5');   
  7. INSERT INTO test VALUES ('12aa5');   
  8. INSERT INTO test VALUES ('12AB5');   
  9. INSERT INTO test VALUES ('ABCDE');   
  10. INSERT INTO test VALUES ('123-5');   
  11. INSERT INTO test VALUES ('12.45');   
  12. INSERT INTO test VALUES ('1a4b5');   
  13. INSERT INTO test VALUES ('1 3 5');   
  14. INSERT INTO test VALUES ('1  45');   
  15. INSERT INTO test VALUES ('1   5');   
  16. INSERT INTO test VALUES ('a  b  c  d');   
  17. INSERT INTO test VALUES ('a b  c   d    e');   
  18. INSERT INTO test VALUES ('a              e');   
  19. INSERT INTO test VALUES ('Steven');   
  20. INSERT INTO test VALUES ('Stephen');   
  21. INSERT INTO test VALUES ('111.222.3333');   
  22. INSERT INTO test VALUES ('222.333.4444');   
  23. INSERT INTO test VALUES ('333.444.5555');   
  24. COMMIT;  
Sql代码
  1. CREATE TABLE test (  
  2. testcol VARCHAR2(50));  
  3.   
  4. INSERT INTO test VALUES ('abcde');  
  5. INSERT INTO test VALUES ('12345');  
  6. INSERT INTO test VALUES ('1a4A5');  
  7. INSERT INTO test VALUES ('12a45');  
  8. INSERT INTO test VALUES ('12aBC');  
  9. INSERT INTO test VALUES ('12abc');  
  10. INSERT INTO test VALUES ('12ab5');  
  11. INSERT INTO test VALUES ('12aa5');  
  12. INSERT INTO test VALUES ('12AB5');  
  13. INSERT INTO test VALUES ('ABCDE');  
  14. INSERT INTO test VALUES ('123-5');  
  15. INSERT INTO test VALUES ('12.45');  
  16. INSERT INTO test VALUES ('1a4b5');  
  17. INSERT INTO test VALUES ('1 3 5');  
  18. INSERT INTO test VALUES ('1  45');  
  19. INSERT INTO test VALUES ('1   5');  
  20. INSERT INTO test VALUES ('a  b  c  d');  
  21. INSERT INTO test VALUES ('a b  c   d    e');  
  22. INSERT INTO test VALUES ('a              e');  
  23. INSERT INTO test VALUES ('Steven');  
  24. INSERT INTO test VALUES ('Stephen');  
  25. INSERT INTO test VALUES ('111.222.3333');  
  26. INSERT INTO test VALUES ('222.333.4444');  
  27. INSERT INTO test VALUES ('333.444.5555');  
  28. COMMIT;  


REGEXP_INSTR
REGEXP_INSTR(<source_string>, <pattern>, <start_position>, <occurrence>, <return_option>, <match_parameter>)

Find words beginning with 's' or 'r' or 'p' followed by any 4 alphabetic characters: case insensitive
源码 复制 打印
  1. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT   
  2. FROM dual;   
  3.   
  4. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 1, 'i') RESULT   
  5. FROM dual;   
  6.   
  7. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 0, 'i') RESULT   
  8. FROM dual;   
  9.   
  10. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 1, 'i') RESULT   
  11. FROM dual;  
Sql代码
  1. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT  
  2. FROM dual;  
  3.   
  4. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 1, 'i') RESULT  
  5. FROM dual;  
  6.   
  7. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 0, 'i') RESULT  
  8. FROM dual;  
  9.   
  10. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 1, 'i') RESULT  
  11. FROM dual;  


Find the postiion of try, trying, tried or tries
源码 复制 打印
  1. SELECT REGEXP_INSTR('We are trying to make the subject easier.', 'tr(y(ing)?|(ied)|(ies))') RESULTNUM   
  2. FROM dual;  
Sql代码
  1. SELECT REGEXP_INSTR('We are trying to make the subject easier.', 'tr(y(ing)?|(ied)|(ies))') RESULTNUM  
  2. FROM dual;  



REGEXP_LIKE

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

AlphaNumeric Characters
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{5}');  
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{5}');  


Alphabetic Characters:
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{5}');  
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{3}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{5}');  


Control Characters
源码 复制 打印
  1. INSERT INTO test VALUES ('zyx' || CHR(13) || 'wvu');   
  2. COMMIT;   
  3.   
  4. SELECT *   
  5. FROM test   
  6. WHERE REGEXP_LIKE(testcol, '[[:cntrl:]]{1}');  
Sql代码
  1. INSERT INTO test VALUES ('zyx' || CHR(13) || 'wvu');  
  2. COMMIT;  
  3.   
  4. SELECT *  
  5. FROM test  
  6. WHERE REGEXP_LIKE(testcol, '[[:cntrl:]]{1}');  


Digits
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:digit:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{5}');  
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:digit:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{3}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{5}');  



Lower Case
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:lower:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}');   
  12.   
  13. SELECT *   
  14. FROM test   
  15. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{5}');  
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:lower:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{2}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}');  
  12.   
  13. SELECT *  
  14. FROM test  
  15. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{5}');  



Printable Characters
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');   
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');  


Punctuation
源码 复制 打印
  1. TRUNCATE TABLE test;   
  2.   
  3. SELECT *   
  4. FROM test   
  5. WHERE REGEXP_LIKE(testcol, '[[:punct:]]');  
Sql代码
  1. TRUNCATE TABLE test;  
  2.   
  3. SELECT *  
  4. FROM test  
  5. WHERE REGEXP_LIKE(testcol, '[[:punct:]]');  



Spaces
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:space:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');   
  12.   
  13. SELECT *   
  14. FROM test   
  15. WHERE REGEXP_LIKE(testcol, '[[:space:]]{5}');  
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:space:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');  
  12.   
  13. SELECT *  
  14. FROM test  
  15. WHERE REGEXP_LIKE(testcol, '[[:space:]]{5}');  




Upper Case
源码 复制 打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:upper:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');   
Sql代码
  1. SELECT *  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '[[:upper:]]');  
  4.   
  5. SELECT *  
  6. FROM test  
  7. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}');  
  8.   
  9. SELECT *  
  10. FROM test  
  11. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');   



Values Starting with 'a%b'
源码 复制 打印
  1. SELECT testcol   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '^ab*');   
Sql代码
  1. SELECT testcol  
  2. FROM test  
  3. WHERE REGEXP_LIKE(testcol, '^ab*');   



'a' is the third value
源码 复制 打印
  1. SELECT testcol   
  2. ROM test  WHERE REGEXP_LIKE(testcol, '^..a.');   
Sql代码
  1. SELECT testcol  
  2. ROM test  WHERE REGEXP_LIKE(testcol, '^..a.');   



Contains two consecutive occurances of the letter 'a' or 'z'
源码 复制 打印
  1. SELECT testcol  FROM test  WHERE REGEXP_LIKE(testcol, '([az])\1', 'i');   
Sql代码
  1. SELECT testcol  FROM test  WHERE REGEXP_LIKE(testcol, '([az])\1', 'i');   


Begins with 'Ste' ends with 'en' and contains either 'v' or 'ph' in the center
源码 复制 打印
  1. SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');   
Sql代码
  1. SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');   



Use a regular expression in a check constraint
源码 复制 打印
  1. CREATE TABLE mytest (c1 VARCHAR2(20),    
  2. CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$')));    
  3. Identify SSN   
  4.   
  5. Thanks: Byron Bush HIOUG    
  6.   
  7.   
  8. CREATE TABLE ssn_test (   
  9. ssn_col  VARCHAR2(20));   
  10.   
  11. INSERT INTO ssn_test VALUES ('111-22-3333');   
  12. INSERT INTO ssn_test VALUES ('111=22-3333');   
  13. INSERT INTO ssn_test VALUES ('111-A2-3333');   
  14. INSERT INTO ssn_test VALUES ('111-22-33339');   
  15. INSERT INTO ssn_test VALUES ('111-2-23333');   
  16. INSERT INTO ssn_test VALUES ('987-65-4321');   
  17. COMMIT;   
  18.   
  19. SELECT ssn_col   
  20. from ssn_test   
  21. WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');   
Sql代码
  1. CREATE TABLE mytest (c1 VARCHAR2(20),   
  2. CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$')));   
  3. Identify SSN  
  4.   
  5. Thanks: Byron Bush HIOUG   
  6.   
  7.   
  8. CREATE TABLE ssn_test (  
  9. ssn_col  VARCHAR2(20));  
  10.   
  11. INSERT INTO ssn_test VALUES ('111-22-3333');  
  12. INSERT INTO ssn_test VALUES ('111=22-3333');  
  13. INSERT INTO ssn_test VALUES ('111-A2-3333');  
  14. INSERT INTO ssn_test VALUES ('111-22-33339');  
  15. INSERT INTO ssn_test VALUES ('111-2-23333');  
  16. INSERT INTO ssn_test VALUES ('987-65-4321');  
  17. COMMIT;  
  18.   
  19. SELECT ssn_col  
  20. from ssn_test  
  21. WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');   


REGEXP_REPLACE

Syntax REGEXP_REPLACE(<source_string>, <pattern>,<replace_string>, <position>, <occurrence>, <match_parameter>)


Looks for the pattern xxx.xxx.xxxx and reformats pattern to (xxx) xxx-xxxx col testcol format a15
col result format a15
源码 复制 打印
  1. SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',   
  2. '(\1) \2-\3') RESULT   
  3. FROM test   
  4. WHERE LENGTH(testcol) = 12;   
Sql代码
  1. SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',  
  2. '(\1) \2-\3') RESULT  
  3. FROM test  
  4. WHERE LENGTH(testcol) = 12;   



Put a space after every character
源码 复制 打印
  1. SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT   
  2. FROM test   WHERE testcol like 'S%';   
Sql代码
  1. SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT  
  2. FROM test   WHERE testcol like 'S%';   



Replace multiple spaces with a single space

源码 复制 打印
  1. SELECT REGEXP_REPLACE('500    Oracle    Parkway, Redwood    Shores, CA', '( ){2,}', ' ') RESULT   
  2. FROM dual;   
Sql代码
  1. SELECT REGEXP_REPLACE('500    Oracle    Parkway, Redwood    Shores, CA', '( ){2,}', ' ') RESULT  
  2. FROM dual;   



Insert a space between a lower case character followed by an upper case character
源码 复制 打印
  1. SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY   
  2. FROM dual;   
Sql代码
  1. SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY  
  2. FROM dual;   

Replace the period with a string (note use of '\')
源码 复制 打印
  1.   
  2. SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE   
  3. FROM dual;   
Sql代码
  1. SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE  
  2. FROM dual;   


REGEXP_SUBSTR

Syntax REGEXP_SUBSTR(source_string, pattern[, position [, occurrence[, match_parameter]]])


Searches for a comma followed by one or more occurrences of non-comma characters followed by a comma
源码 复制 打印
  1. SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT   
  2. FROM dual;   
Sql代码
  1. SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT  
  2. FROM dual;   



Look for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.) col result format a50
源码 复制 打印
  1. SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database',   
  2. 'http://([[:alnum:]]+\.?){3,4}/?') RESULT   
  3. FROM dual;   
Sql代码
  1. SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database',  
  2. 'http://([[:alnum:]]+\.?){3,4}/?') RESULT  
  3. FROM dual;   



Extracts try, trying, tried or tries


SELECT REGEXP_SUBSTR('We are trying to make the subject easier.','tr(y(ing)?|(ied)|(ies))')
FROM dual;
Extract the 3rd field treating ':' as a delimiter SELECT REGEXP_SUBSTR('system/pwd@orabase:1521:sidval',
'[^:]+', 1, 3) RESULT
FROM dual;

Extract from string with vertical bar delimiter
源码 复制 打印
  1.   
  2. CREATE TABLE regexp (   
  3. testcol VARCHAR2(50));   
  4.   
  5. INSERT INTO regexp   
  6. (testcol)   
  7. VALUES  
  8. ('One|Two|Three|Four|Five');   
  9.   
  10. SELECT * FROM regexp;   
  11.   
  12. SELECT REGEXP_SUBSTR(testcol,'[^|]+', 1, 3)   
  13. FROM regexp;   
Sql代码
  1. CREATE TABLE regexp (  
  2. testcol VARCHAR2(50));  
  3.   
  4. INSERT INTO regexp  
  5. (testcol)  
  6. VALUES  
  7. ('One|Two|Three|Four|Five');  
  8.   
  9. SELECT * FROM regexp;  
  10.   
  11. SELECT REGEXP_SUBSTR(testcol,'[^|]+', 1, 3)  
  12. FROM regexp;  








环境: oracle 10g r2
更多内容可以参考:
<<Oracle? Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2)>>

必须明白的是，oracle的正则表达式和其它的语言的并不是完全一样。
oracle支持以下标准的正则表达式:

  • IEEE Portable Operating System Interface (POSIX) standard draft 1003.2/D11.2
  • Unicode Regular Expression Guidelines of the Unicode Consortium
  • Extends the matching capabilities for multilingual data beyond what is specified  in the POSIX standard(即posix的多语言支持).
  • Adds support for the common Perl regular expression extensions that are not
          included in the POSIX standard but do not conflict with it. Oracle Database
provides built-in support for some of the most heavily used Perl regular
expression operators, for example, character class shortcuts, the non-greedy
modifier, and so on(即部分perl标准,不包含在posix标准,但是又不与之冲突的)


---2008-09-08 做了个实验，关键的要点是三个,重复次数{},+,*,?  
---以及匹配模式[...] ,[^...],(...),(...)
--以及转义符号 \d,\D,\\等。
------------------------------------------------------------------------------------------------------------------
                                     ID VALUE
--------------------------------------- ----------------------------------------
                                      9 lzf is a chinese
                                     10 1231231231lzf
                                     11 perfect
                                      1 13625061300
                                      2 059183383335
                                      3 13959029697
                                      4 13950323157
                                      5 who is your daddy?
                                      6 who are you?
                                      6 what is your name?
                                      6 I am 20 years old!
                                      7 股市下挫到2100点左右
                                      8 lzf ' is very happy 136ok
                                      8 do you know lzf,ok?
                                      8 ok,lzf is my brother
------------------------------------------------------------------------------------------------------------------

select * from  test_reg where regexp_like(value,'^1(.+)7$')  --1开头，7结尾的内容
select * from  test_reg where regexp_like(value,'\d+')       --一个或者多个数字
select * from  test_reg where regexp_like(value,'^[a-zA-Z]{1,}[a-zA-Z]+$')  --全部字符的.[:digit:]
select * from  test_reg where regexp_like(value,'^[0-9]{1,}[0-9]+$')  --全部数字的.
select * from  test_reg where regexp_like(value,'[y][o][u]') --包含you
select * from  test_reg where regexp_like(value,'(you)') --包含you
select * from  test_reg where regexp_like(value,'[you]')     --包含y o u的,不分顺序
select * from  test_reg where regexp_like(value,'(lzf){1,}(.){0,}(ok)') --包含lzf 和ok的.  但是也仅仅限制与lzf 在ok前
以上试验是在看了下文以及参考了OReilly.Oracle.PL.SQL.Programming.4th.Edition.Aug.2005.chs后作出的.
--------------
2010/08/04 增加以下例子

---
--以'POSIX('开头的串,以下几个表达式的结果都是一样的。
select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+)(,)([RYN]{1})(,)\d{1,}?\)')

select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+)(,)([RYN]{1})(,)\d+\)')
select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+)(,)([RYN]{1}),\d+\)')
select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+),([RYN]{1}),\d+\)')
select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+),([RYN]+),\d+\)')

select  1 from dual where regexp_like('POSIX(ADDDDDD,Y,13)','^(POSIX\()([A-Z]+),[RYN]+,\d+\)')
select  1 from dual where regexp_like('POSIX(A,N,13)','^(POSIX\()([A-Z]+),[RYN]+,\d+\)')
----------
--位于字符串中的串
select  1 from dual where regexp_like('POSIX(A,N,13)','^(POSIX\()([A-Z]+),[RYN]+,\d+\)')
select  1 from dual where regexp_like('P_OSI_X(JYL_GP_R,N,13)','^[A-Z_]+\(([A-Z_]+),[RYN]+,\d+\)')

--测试regexp_substr,基本的还是regexp_like
 SQL> select regexp_substr('P_OSI_X(JYL_GP_R,N,13)+AVG(JYL_JJ_R,Y,3)','^[A-Z_]+\(([A-Z_]+),[RYN]+,\d+\)') FROM DUAL
  2  /
 
REGEXP_SUBSTR('P_OSI_X(JYL_GP_
------------------------------
P_OSI_X(JYL_GP_R,N,13)
 
SQL> select regexp_substr('P_OSI_X(JYL_GP_R,N,13)+AVG(JYL_JJ_R,Y,3)','[A-Z_]+\(([A-Z_]+),[RYN]+,\d+\)',1,2) FROM DUAL
  2  /
 
REGEXP_SUBSTR('P_OSI_X(JYL_GP_
------------------------------
AVG(JYL_JJ_R,Y,3)

2010/08/26 新增例子
-- delete the first ()
 select regexp_replace('dad(disc_id=50003118)add','(^([A-Z]{1,}\()|(\)[A-Z]{1,})$)','',1,0,'i') FROM DUAL
--
match pattern
using classes
select regexp_substr(upper('dad(ssdsd89\9 dd== (sddf(d)),sdsd)add'),'^([A-Z]{1,}\()[[:space:][:alnum:]\,\_\(\)\=]{1,}\)[A-Z]{1,}$',1,1,'i') FROM DUAL
  
OReilly. 的参考内容
Section A.1.  Metacharacters

A.1. Metacharacters

The R2 column in Tables A-1 through A-3 indicates which metacharacters were introduced in Oracle Database 10g Release 2. All others were introduced in Release 1.

Table A-1. Character-matching metacharacters

Syntax

R2?

Description

.

 

Matches any single character except for newline. Will match newline when the n flag is set. On Windows, Linux, and Unix platforms, chr(10) is recognized as the newline.

[ ... ]

 

Defines a matching list that matches any character listed between the brackets. You may specify ranges of characters, as in a-z. These ranges are interpreted based on the NLS_SORT setting.

A dash (-) is a literal when it occurs first or last in the list (e.g., [abc-]). A closing-bracket (]) is a literal when it occurs first in the list (e.g., []abc]). A caret (^) in the first position makes the list anonmatching list (see the next entry).

[^ ... ]

 

Matches any character not listed between the brackets. Referred to as a "nonmatching list."

[:class:]

 

Matches any character that belongs to the specified character class. May only be used within a matching list: [[:class:]abc] is a valid expression, [:class:]abc is not.Table A-5 lists the valid character class names.

[.coll.]

 

Matches the specified collation element, which may be one or more characters. May only be used within a matching list. For example, the expression [[.ch.]] matches the Spanish letter "ch".Table A-4 lists the valid collation elements.

[=char=]

 

Matches all characters that share the same base character as char. May be used only within a matching list. For example, [[=e=]] matches any of: "eé?è???E".

\d


Matches any digit. Equivalent to [[:digit:]].

\D


Matches any nondigit. Equivalent to [^[:digit:]]

\w


Matches any "word character." Word characters are defined to be alphabetic characters, numeric characters, and the underscore.

\W


Matches any nonword character.

\s


Matches any whitespace character. Equivalent to [[:space:]].

\S


Matches nonwhitespace characters. Equivalent to [^[:space:]].

Table A-2. Quantifiers

Syntax

R2?

Description

+

 

One or more

?

 

Zero or one

*

 

Zero or more

{m}

 

Exactly m occurrences

{m,}

 

At least m occurrences

{m,n}

 

At least m, and at most n occurrences

+?


One or more, but nongreedy

??


Zero or one, but nongreedy

{m}?


The same as {m}

{m,}?

At least m occurrences, but nongreedy and stops as soon asm occurrences are reached

{m,n}?


At least m, and at most n occurrences, but nongreedy; when possible,m occurrences are matched

Table A-3. Other metacharacters

Syntax

R2?

Description

|

 

Specifies an alternation. An alternation within a subexpression doesn't extend beyond the subexpression.

( ...)

 

Defines a subexpresson.

\n

 

References the text matched by the nth subexpression. Backreferences may range from \1 through \9.

\

 

When not followed by a digit, the \ is an escape character. For example, use the pattern \\1 to look for a single backslash followed by the digit 1, use \( to look for an opening-parentheses (rather than begin a subexpression), etc.

^

 

Anchors an expression to the beginning of the string (in multiline mode, to the beginning of a line).

$

 

Anchors an expression to the end of the string (in multiline mode, to the end of a line).

\A


Anchors an expression to the beginning of the string regardless of whether multiline mode is specified.

\Z


Anchors an expression to the end of the string, or the a newline that happens to be ending a string, regardless of whether multiline mode is specified.

\z


Anchors an expression to the end of the string regardless of whether multiline mode is specified.

Table A-4. Collation elements

NLS_SORT

Multicharacter collation elements

XDANISH

aa

oe

AA

OE

Aa

Oe

XSPANISH

ch

ll

CH

LL

Ch

Ll

XHUNGARIAN

cs

gy

ly

ny

sz

ty

zs

CS

GY

LY

NY

SZ

TY

ZS

Cs

Gy

Ly

Ny

Sz

Ty

Zs

XCZECH

ch

CH

Ch

XCZECH_PUNCTUATION

ch

CH

Ch

XSLOVAK

dz

d_

ch

DZ

D_

CH

Dz

D_

Ch

XCROATIAN

d_

lj

nj

D_

LJ

Nj

D_

Lj

NJ

Table A-5. Supported character classes

Class

Description

[:alnum:]

Alphanumeric characters (same as [:alpha:] + [:digit:])

[:alpha:]

Alphabetic characters only

[:blank:]

Blank space characters, such as space and tab

[:cntrl:]

Nonprinting, or control characters

[:digit:]

Numeric digits

[:graph:]

Graphical characters (same as [:punct:] + [:upper:] + [:lower:] + [:digit:])

[:lower:]

Lowercase letters

[:print:]

Printable characters

[:punct:]

Punctuation characters

[:space:]

Whitespace characters such as space, formfeed, newline, carriage return, horizontal tab, and vertical tab

[:upper:]

Uppercase letters

[:xdigit:]

Hexadecimal characters


对于类的使用，要特别注意点，因为基本上要包含在一对中括号中。
例一：检验非汉字的函数
例如可以定义一个检验非汉字的函数，不考虑其它UNICODE
CREATE OR REPLACE Function func_existNormalChar(chars In Varchar2) Return Pls_Integer
Is
  vResult Pls_Integer;
Begin
  Select 1
     Into vresult From dual
        Where regexp_like(chars,'[!-~ ，。、？’“；：『』【】＋＝－——～！◎＃￥％……※×（）]+')
        Or    regexp_like(chars,'[[:space:]+]')
        Or    regexp_like(chars,'[[:blank:]+]')
        Or    regexp_like(chars,'[[:cntrl:]+]');
  Return vresult;
Exception
  When Others Then
    Return 0;
End;

例二：出现次数的检验
 序号 例子 结果 说明
 1regexp_replace('a12c','[[:digit:]]+','*') a*c 测试"+",即出现一次或者更多次的.由于采取的是连续的匹配,所以结果上是把多个数字合并为一个替代字符串,为了理解连续匹配的意思,看序号9更能够明白.这是总体偏少的模式
 2regexp_replace('a12c','[[:digit:]]?','*') *a***c* 测试"?",即0或者一次.这个符号,会在任意一个其它字符边上插入一个假设出现的匹配类型符号,如例子,a的左边本来是没有,但是表达式认为数字字符在a的左边出现了0次(这个是成立的).然后还要逐个替换过去,这是总体偏多的模式,过于匹配和逐个匹配结合
 3regexp_replace('a12c','[[:digit:]]*','*')
 *a**c* 测试"*",即0或者更多次数,这个模式类似与?类似,采取连续匹配+多余匹配
 4regexp_replace('a12c','[[:digit:]]+?','*')
 a**c 测试"+?",匹配在前面一次或者多次的串,采取的是逐个匹配的策略(如果有连续的情况出现),这个是通常最希望使用的模式.
 5regexp_replace('a12c','[[:digit:]]*?','*') *a*1*2*c* 测试"*?",匹配0或者更多次,难于理解! 但可以简单地理解为为在各个字符中以及两边插入替代串.
 6regexp_replace('a12c','[[:digit:]]??','*') *a*1*2*c* 测试"??",匹配0或者1次,难于理解啊!但可以简单地理解为为在各个字符中以及两边插入替代串.
 7regexp_replace('a12c','[[:digit:]]{1,}','*')
 a*c 测试{1,},同+,结果已经说明
 8   
 9regexp_replace('a1x2c','[[:digit:]]+','*') a*x*c测试"+",这个例子说明,如果被检测的字符串中特定的匹配串如果不是连续的(多次),那么替代的还是属于正常的范围.


其它的次数模式暂时没有测试,对于 *,?,+ ,*?,+?,??,基本有其对应的模式 {n,m}? 或者是{n,}? ,除了*,*?之外,因为此二者表示的都是0或者多次(大于1),无法用{n,m}的方式表示.

例三:多个类的并用
很多时候使用类还是很方便的,例如[:digit:]表示数字.[:space:]表示空格,换行,回车等等.
如果希望把特定串中的数字和回车空格等都替换为'-',则可以使用 "|"(替代操作符,其实就是类似一般高级语言中的或操作符号).
select  regexp_replace('adfa jkjk jk
jk','([[:digit:]]+?)|([[:space:]]+?)','-')  from dual;
结果如下:
adfa-jkjk-jk-jk
已经把空格和回车符号都替换为'-'了.
又例如:
Select regexp_replace('a1b c1d jk
jk','([[:digit:]]+?)|([[:space:]]+?)','-') From dual
结果如下:
a-b-c-d-jk-jk

例四:迭代替换
例如希望把以下串替换掉
'<xxxx   <often>  <common>   <fat>   >'

关于范围操作符号 -

发音：横杆或者减号

在列表匹配操作情况下，如果没有特别的设置，就是表示匹配一个范围：

例子：把字符串中非数字+-()的符号替换为空

这是一个不成功的匹配

SQL> select regexp_replace('1+[2c- 3([ad]km,4','[^0-9\+\-\(\)]','',1) from dual;
 
REGEXP_REPLACE('1+[2C-3([AD]KM
------------------------------
1+23(4

这也是一个错误的匹配，因为减号这个时候表示一个范围
SQL> select regexp_replace('1+[2c- 3([ad]km,4','[^0-9\+-\(\)]','',1) from dual;
 
REGEXP_REPLACE('1+[2C-3([AD]KM
------------------------------
1+[2-3([,4

二者的区别在于单独的一个减号，如果前面没有跟上斜杠\，那么就是表示范围，否则就是减号本身。

但即使如此，依然没有达到目的，第一个把减号还是给去除了，虽然排除了其它的需要排除的符号，

第二个，则是当作范围符号，大部分的没有排除掉。

但是不太明白的是，按照oracle文档的约定，在范围匹配中形如[\-]表示匹配一个减号，为何依然匹配补上了，尤其是当夹着其它需要匹配的模式的时候。

现在只能尝试着把这个模式放在最后：

SQL> select regexp_replace('1+[2c- 3([ad4','[^0-9\(+\)\-]','',1) from dual
  2  /
 
REGEXP_REPLACE('1+[2C-3([AD4',
------------------------------
1+2-3(4

结果是可以，因为模式"\-"被放到了范围中的尾巴。

那是文档不全，还是理解不透彻了，

看来以后遇到这样的问题还是先把减号放到尾巴去吧。


转自：http://www.iteye.com/topic/398717

            http://qjoycn.iteye.com/blog/1280595

            http://mayuqi827.iteye.com/blog/1008395

            http://lzfhope.blog.163.com/blog/static/6363992200812112025877/

            http://blog.csdn.net/xyz121323693/article/details/7727746
rznice
关注
  • 1
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
ORACLE正则表达式
05-07
学习使用ORACLE正则表达式,运用regexp_like进行模糊匹配.
oracle正则表达式(详细)
微风
06-28 1万+
一、ORACLE中的支持正则表达式的函数主要有四个： 1，REGEXP_LIKE ：与LIKE的功能相似 2，REGEXP_INSTR ：与INSTR的功能相似 3，REGEXP_SUBSTR ：与SUBSTR的功能相似 4，REGEXP_REPLACE ：与REPLACE的功能相似
Oracle正则表达式
qq_33439938的博客
08-09 1万+
1. 基本介绍-Oracle支持正则表达式的函数 Oracle中一般常用到正则表达式的函数主要有一下5个： REGEXP_LIKE：与LIKE的功能相似； REGEXP_SUBSTR ：与SUBSTR的功能相似； REGEXP_INSTR ：与INSTR的功能相似； REGEXP_REPLACE ：与REPLACE的功能相似； REGEXP_COUNT ：与COUNT的功能相似； 2. 分类介...
oracle中正则表达式规则,Oracle正则表达式规则
weixin_39945178的博客
04-03 395
Oracle 终于在10g中加入了对正则表达式的支持，下面就来了解一下吧！匹配字符：字符功能^标记行的开始$标记行的结束= =匹配字符族，如：[=a=] 可匹配字符a、â、ã、ä、å等.匹配任何字符(除了NULL)[:alnum:]匹配任何字母和数字[:alpha:]匹配任何字母[:blank:]匹配空格[:cntrl:]匹配任何控制字符(在ASCII码表中不可打印的字符，如ESC键)[:digi...
Oracle 正则表达式详解（regexp_substr、regexp_instr、regexp_replace、regexp_like、regexp_count）
热门推荐
鱼丸丶粗面
07-22 2万+
文章目录1 概述2 匹配规则3 正则函数3.1 regexp_substr()3.2 regexp_instr()3.3 regexp_replace()3.4 regexp_like() 1 概述 1. 作用：处理字符时，很强大 2. 分类：与下列相似，当功能更加强大（'支持正则表达式'） (1) regexp_like : 同 like 功能相似（模糊 '匹配'） (2) regexp_instr : 同 instr 功能相似（返回字符所在 '下标'） (3) regexp_s
oracle最常用的名称解析方法,ORACLE 对象名称解析步骤
weixin_42513522的博客
04-11 216
Managing Object Name ResolutionObject names referenced in SQL statements can consist of several pieces, separated byperiods. The following describes how the database resolves an object name.1. Oracle ...
oracle正则表达式
weixin_30607029的博客
05-27 415
oracle的正则表达式(regularexpression) 特殊字符：'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。 '$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'n'或'r'。 '.'匹配除换行符n之外的任何单字符。 '?'匹配前面的子表达式零次或一次。 '*'匹配前面的子...
在 Oracle 中使用正则表达式
Hehuyi_In的博客
03-14 2440
Oracle使用正则表达式离不开这4个函数： regexp_like regexp_substr regexp_instr regexp_replace 看函数名称大概就能猜到有什么用了。 regexp_like函数，只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配，语法很简单： regexp_substr函数，和 substr 类似，用于获取符合正...
oracle 的正则表达式
lihaiming_2008的专栏
01-05 130
oracle的正则表达式(regular expression)简单介绍 目前，正则表达式已经在很多软件中得到广泛的应用，包括*nix（Linux, Unix等），HP等操作系统，PHP，C#，Java等开发环境。 Oracle 10g正则表达式提高了SQL灵活性。有效的解决了数据有效性，重复词的辨认, 无关的空白检测，或者分解多个正则组成的字符串等问题。 Oracle 10g支持正则表达式...
Oracle 查询的十个小技巧
weixin_30511107的博客
07-29 525
Oracle数据库查询十个小技巧 数据查询，是数据库操作中最主要的功能之一；有时候数据库查询性能的好坏，直接关系到数据库的运行效率，关系到数据库的选型。下面笔者不谈大道理，只是对其中对一些平时大家容易忽略的查询小技巧做一些总结。或许大家可能正在为此犯愁呢? 　　第一个技巧：利用连接符连接多个字段。　　如在员工基本信息表中，有员工姓名、员工职位、出身日期等等。如果现在视图中这三个字段显示在同一个...
oracle 模糊查询忽略大小写
weixin_33670713的博客
12-03 1205
select * from c_table t where upper(t.name) like '%F%';--将字段全转成大写 select * from c_table t where lower(t.name) like '%f%';--将字段全转成小写 select * from c_table t where t.name like upper('%f%') or t.name l...
Oracle 10G:PL/SQL正规表达式（正则表达式）手册
01-21
Oracle 的正规表达式的实施是以各种 SQL 函数和一个 WHERE 子句操作符的形式出现的。如果您不熟悉正规表达式，那么这篇文章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能。已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语言的环境中应用这种功能。 什么是正规表达式？　　正规表达式由一个或多个字符型文字和/或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式 cat。它被读作字母 c，接着是字母 a 和 t，这种模式匹配 cat、location 和 catalog 之类的字符串。元字符提供算法来确定 Oracle 如何处理组成一个正规
oracle 解决匹配的几种方法
touchmm
04-24 653
//数据 with ta as( select 1 id,'f1,f2' fid from dual union all select 2,'f4,f5,f6' from dual) ,tb as( select 'f1' fid,'name1' fname from dual union all select 'f2','name2' from d...
String.matches()的用法
weixin_30314631的博客
10-12 83
http://hi.baidu.com/tiangel/blog/item/e9c108faed2e138b9f5146cf.html String.matches() 这个方法主要是返回是否匹配指定的字符串，如果匹配则为true,否则为false; 如：/** * 判断字符创是否是一个有效的日期 * * @param theStr * @return tru...
Oracle正则表达式匹配
wj的博客
12-16 1万+
Oracle从10g开始支持正则表达式 元字符（metacharacters） POSIX 正则表达式由标准的元字符（metacharacters）所构成： '^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。 '$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹 配 '\n' 或 '\r'。 '.' 匹配除换行符之外的任何单字符。 '?' 匹配前面的子表达式零次或一次。 '+' 匹配前面的子表达式一次或多次。 '*' 匹
oracle中正则的使用
胡家伟的博客
07-21 247
目录 oracle中正则使用: 模式匹配 条件查询 备份表时候，oracle11g的表名最长是30个字符，所以当备份的时候，加上前缀bak_0712这种就不行了,如果需要备份的表比较多的时候,挨个重 命名很麻烦,(命名本身就是一个很让人头疼的事情),所以我直接采用首字母的方式 例如原表名为 'TD_RESOURCE_MODIFY_RECORD' 那么备份表为:BAK_0712_TD_...
Oracle中的正则表达式
m0_37374843的博客
09-13 309
Oracle中的正则表达式离不开这4个函数： 1. regexp_like 2. regexp_substr 3. regexp_instr 4. regexp_replace 看函数名称大概就能猜到有什么用了。 regexp_like 只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配，语法很简单： regexp_substr 函数，和 substr...
强密码正则表达式
weixin_33806914的博客
09-28 1788
^(?![0-9]+$)(?![a-zA-Z]+$)(?!([^(0-9a-zA-Z)]|[\(\)])+$)([^(0-9a-zA-Z)]|[\(\)]|[a-zA-Z]|[0-9]){6,}$ 数字字母特殊字符组合
oracle 正则表达式
最新发布
08-26
Oracle 提供了正则表达式功能，可以在 SQL 查询中使用正则表达式进行模式匹配。下面是一些关于 Oracle 正则表达式的常见用法和示例： 1. REGEXP_LIKE 函数：用于判断一个字符串是否匹配指定的正则表达式。 示例：SELECT * FROM employees WHERE REGEXP_LIKE(last_name, 'Smi[th]'); 2. REGEXP_REPLACE 函数：用于替换字符串中与正则表达式模式匹配的部分。 示例：SELECT REGEXP_REPLACE('Hello 123 World', '[0-9]+', ' ') FROM dual; 3. REGEXP_INSTR 函数：返回一个字符串中与正则表达式模式匹配的子串的位置。 示例：SELECT REGEXP_INSTR('Hello World', 'World') FROM dual; 4. REGEXP_SUBSTR 函数：返回一个字符串中与正则表达式模式匹配的子串。 示例：SELECT REGEXP_SUBSTR('Hello World', 'W[a-z]+') FROM dual; 5. REGEXP_COUNT 函数：返回一个字符串中与正则表达式模式匹配的次数。 示例：SELECT REGEXP_COUNT('Hello World Hello', 'Hello') FROM dual; 以上仅是 Oracle 正则表达式的基本用法，您还可以使用更复杂的正则表达式模式来满足不同的需求。希望这些信息能对您有所帮助！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值