Oracle SQL 语句中正则表达式的应用 - 数据库编程

O 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. W

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