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 followed by a substring of one or more alphanumeric characters and optionally, a period (.) col result format a50
1. select regexp_substr('go to and click on database',
2. 'http://([[:alnum:]]+\.?){3,4}/?') result
3. from dual;
select regexp_substr('go to 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;