Oracle SQL 语句中关于正则表达式的函数有
REGEXP_LIKE(匹配)
REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)
如 手机号码的表达式: ^[1]{1}[35]{1}[[:digit:]]{9}$
查询客户信息表(tKHXX)中有手机号码(SJHM)的可以这样查询
- 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
下面是个测试例子及环境
测试表
- 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;
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
- 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;
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
- SELECT REGEXP_INSTR('We are trying to make the subject easier.', 'tr(y(ing)?|(ied)|(ies))') RESULTNUM
- 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(<source_string>, <pattern>, <match_parameter>)
AlphaNumeric Characters
- 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}');
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:
- 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}');
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
- INSERT INTO test VALUES ('zyx' || CHR(13) || 'wvu');
- COMMIT;
- SELECT *
- FROM test
- 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
- 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}');
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
- 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}');
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
- 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}');
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
- TRUNCATE TABLE test;
- SELECT *
- FROM test
- WHERE REGEXP_LIKE(testcol, '[[:punct:]]');
TRUNCATE TABLE test; SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:punct:]]');
Spaces
- 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}');
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
- 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}');
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'
- SELECT testcol
- FROM test
- WHERE REGEXP_LIKE(testcol, '^ab*');
SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^ab*');
'a' is the third value
- SELECT testcol
- 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'
- 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
- 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
- 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}$');
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(<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
- SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
- '(\1) \2-\3') RESULT
- FROM test
- 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
- SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT
- 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
- SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') RESULT
- 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
- SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY
- FROM dual;
SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY FROM dual;
Replace the period with a string (note use of '\')
- SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE
- 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
- SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT
- 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
- SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database',
- 'http://([[:alnum:]]+\.?){3,4}/?') RESULT
- 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
- 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;
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;