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
[: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 INT