当前位置:  数据库>oracle

Oracle SQL 语句中正则表达式的应用

    来源: 互联网  发布时间：2017-03-26

    本文导语: 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}$...

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}$' ) 
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'); 
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; 




























CREATE TABLE test ( testcol VARCHAR2(50)); INSERT INTO test VALUES ('abcde'); INSERT INTO test VALUES ('12345'); INSERT INTO test VALUES ('1a4A5'); INSERT INTO test VALUES ('12a45'); INSERT INTO test VALUES ('12aBC'); INSERT INTO test VALUES ('12abc'); INSERT INTO test VALUES ('12ab5'); INSERT INTO test VALUES ('12aa5'); INSERT INTO test VALUES ('12AB5'); INSERT INTO test VALUES ('ABCDE'); INSERT INTO test VALUES ('123-5'); INSERT INTO test VALUES ('12.45'); INSERT INTO test VALUES ('1a4b5'); INSERT INTO test VALUES ('1 3 5'); INSERT INTO test VALUES ('1 45'); INSERT INTO test VALUES ('1 5'); INSERT INTO test VALUES ('a b c d'); INSERT INTO test VALUES ('a b c d e'); INSERT INTO test VALUES ('a e'); INSERT INTO test VALUES ('Steven'); INSERT INTO test VALUES ('Stephen'); INSERT INTO test VALUES ('111.222.3333'); INSERT INTO test VALUES ('222.333.4444'); INSERT INTO test VALUES ('333.444.5555'); COMMIT;
REGEXP_INSTR
REGEXP_INSTR(, , , , , )

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; 










SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual; SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 1, 'i') RESULT FROM dual; SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 0, 'i') RESULT FROM dual; SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 1, 'i') RESULT 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; 

SELECT REGEXP_INSTR('We are trying to make the subject easier.', 'tr(y(ing)?|(ied)|(ies))') RESULTNUM FROM dual;
REGEXP_LIKE

REGEXP_LIKE(, , )

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}'); 










SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alnum:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}'); SELECT * FROM test 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}'); 











SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{3}'); SELECT * FROM test 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}'); 






INSERT INTO test VALUES ('zyx' || CHR(13) || 'wvu'); COMMIT; SELECT * FROM test 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}'); 











SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:digit:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:digit:]]{3}'); SELECT * FROM test 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}'); 















SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}'); SELECT * FROM test 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}'); 











SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');
Punctuation
1. TRUNCATE TABLE test; 
2.  
3. SELECT * 
4. FROM test 
5. WHERE REGEXP_LIKE(testcol, '[[:punct:]]'); 





TRUNCATE TABLE test; SELECT * FROM test 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}'); 















SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}'); SELECT * FROM test 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}');  











SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');
Values Starting with 'a%b'

1. SELECT testcol 
2. FROM test 
3. WHERE REGEXP_LIKE(testcol, '^ab*');  

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^ab*');
'a' is the third value

1. SELECT testcol 
2. ROM test  WHERE REGEXP_LIKE(testcol, '^..a.');  

SELECT testcol FROM 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');  

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$');  

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}$');  



















CREATE TABLE mytest (c1 VARCHAR2(20), CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$'))); Identify SSN Thanks: Byron Bush HIOUG CREATE TABLE ssn_test ( ssn_col VARCHAR2(20)); INSERT INTO ssn_test VALUES ('111-22-3333'); INSERT INTO ssn_test VALUES ('111=22-3333'); INSERT INTO ssn_test VALUES ('111-A2-3333'); INSERT INTO ssn_test VALUES ('111-22-33339'); INSERT INTO ssn_test VALUES ('111-2-23333'); INSERT INTO ssn_test VALUES ('987-65-4321'); COMMIT; SELECT ssn_col from ssn_test WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$'
REGEXP_REPLACE

Syntax REGEXP_REPLACE(, ,, , , )

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;  


SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3}).([[:digit:]]{3}).([[:digit:]]{4})', '(1) 2-3') RESULT FROM test 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%';  

SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '1 ') RESULT 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;  

SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') RESULT 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;  

SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '1 2') CITY FROM dual;
Replace the period with a string (note use of '')

1. SELECT REGEXP_REPLACE('We are trying to make the subject easier.','.',' for you.') REGEXT_SAMPLE 
2. FROM dual;  

SELECT REGEXP_REPLACE('We are trying to make the subject easier.','.',' for you.') REGEXT_SAMPLE 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;  

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT 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;  

SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+.?){3,4}/?') RESULT 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. 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;  











CREATE TABLE regexp ( testcol VARCHAR2(50)); INSERT INTO regexp (testcol) VALUES ('One|Two|Three|Four|Five'); SELECT * FROM regexp; SELECT REGEXP_SUBSTR(testcol,'[^|]+', 1, 3) FROM regexp;

Equivalence classes
1. SELECT REGEXP_SUBSTR('iSelfSchooling NOT ISelfSchooling', '[[=i=]]SelfSchooling') RESULT  
2. FROM dual;   

SELECT REGEXP_SUBSTR('iSelfSchooling NOT ISelfSchooling', '[[=i=]]SelfSchooling') RESULT FROM dual;


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • oracle用什么SQL语句判断表存不存在
  • 请问怎么用jsp语句删除oracle中的一条记录？
  • Oracle中SQL语句连接字符串的符号使用介绍
  • Oracle用什么语句查询字段？
  • java开源软件 iis7站长之家
  • 请问在 Linux 下如何用代码实现连接oracle数据库 并 执行 SQL 语句？
  • Oracle 9i轻松取得建表和索引的DDL语句
  • Oracle的SQL语句中如何处理‘&’符号
  • 关于Oracle中的sql语句的疑问，向大家请教。
  • Oracle Sql语句长度限制问题及解决
  • Oracle9i取得建表和索引的DDL语句
  • Oracle 中文字段进行排序的sql语句
  • oracle数据库添加或删除一列的sql语句
  • Oracle中查询本月星期5的所有日期列表的语句
  • Oracle中备份表的简单sql命令语句
  • oracle中误删除表后恢复语句(FLASHBACK)
  • Oracle判断指定列是否全部为数字的sql语句
  • jsp中在oracle中查询日期类型时sql语句该怎么写啊？
  • Oracle 常用的SQL语句
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3