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}$" ）
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 lineEquivalence 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
Cause the previous expression to be repeated n timesAlternative 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（<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; 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 tries1. 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_LIKEREGEXP_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}"）; 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 value1. 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 center1. 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 constraint1. 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_REPLACESyntax 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 a151. 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 character1. 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 space1. 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 character1. 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_SUBSTRSyntax 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 comma1. 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 a501. 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 triesSELECT 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;Oracle 约束enable/disable novalidateCentOS 5.3 安装配置Oracle 10g 手动启动及自启动服务相关资讯 oracle

[INS-32052] Oracle基目录和Oracle （07/22/2014 07:41:41）
Oracle 4个大对象（lobs）数据类型（02/03/2013 12:33:05）
Oracle按时间段分组统计（07/26/2012 10:36:48）

[Oracle] dbms_metadata.get_ddl的（07/12/2013 07:37:30）
Liferay Portal 配置使用Oracle和（07/31/2012 20:07:18）
Concurrent Request:Inactive （07/20/2012 07:44:05）

本文评论查看全部评论（2）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款