Application of Regular Expressions in Oracle SQL statements

REGEXP_LIKE (matching)
REGEXP_INSTR (included)
REGEXP_REPLACE (replace)
REGEXP_SUBSTR (extract)

For example, the expression of the mobile phone number: ^ [1] {1} [35] {1} [[: digit:] {9} $
You can query customer information tables (tKHXX) that contain mobile phone numbers (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} $'
Explain this expression
^ Indicates start
$ Indicates end
[] Internal match range
Number of content tables in {}

Mobile phone numbers start with 1 and start with 3 or 5 plus 9 digits.
The expression starting with 1 is ^ [1] {1}, meaning that the START 1 contains 1
3 or 5 expressions: [35] {1}
The end of the 9-digit number is [[: digit:] {9} $ here [: digit:], which is a special writing method. It means adding an ending character to the number. $

The expression is simple and more efficient.
The following are some references from the network :)

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

The following is a test example and environment.
Test Table
1. create table test (
2. testcol VARCHAR2 (50 ));
3.
4. insert into test VALUES ('abcde ');
5. insert into test VALUES ('20140901 ');
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 ('2017-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 ('192. 123 ');
26. insert into test VALUES ('192. 100 ');
27. insert into test VALUES ('192. 100 ');
28. COMMIT;

Create table test (testcol VARCHAR2 (50); insert into test VALUES ('abcde'); insert into test VALUES ('123 '); 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 ('1970-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 ('1970. 111. 222.3333 '); insert into test VALUES ('2017. 333.4444 '); insert into test VALUES ('2017. 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 ('1970 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 1, 0, 'I ') RESULT
2. FROM dual;
3.
4. SELECT REGEXP_INSTR ('1970 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 1, 1, 'I ') RESULT
5. FROM dual;
6.
7. SELECT REGEXP_INSTR ('1970 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 2, 0, 'I ') RESULT
8. FROM dual;
9.
10. SELECT REGEXP_INSTR ('1970 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 2, 1, 'I ') RESULT
11. FROM dual;

SELECT REGEXP_INSTR ('1970 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 1, 0, 'I ') result from dual; SELECT REGEXP_INSTR ('2017 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 1, 1, 'I') RESULT FROM dual; SELECT REGEXP_INSTR ('2017 Oracle Pkwy, Redwood Shores, ca', '[o] [[: alpha:] {3}', 1, 2, 0, 'I') RESULT FROM dual; SELECT REGEXP_INSTR ('2017 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 (<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 value

1. SELECT testcol
2. ROM test WHERE REGEXP_LIKE (testcol, '^ .. .');

SELECT testcol FROM test WHERE REGEXP_LIKE (testcol, '^... .');
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 'php' 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 ('2017-22-3333 ');
12. insert into ssn_test VALUES ('2017 = 22-3333 ');
13. insert into ssn_test VALUES ('2017-A2-3333 ');
14. insert into ssn_test VALUES ('2017-22-33339 ');
15. insert into ssn_test VALUES ('2017-2-23333 ');
16. insert into ssn_test VALUES ('2017-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 ('2017-22-3333 '); insert into ssn_test VALUES ('2017 = 22-3333 '); insert into ssn_test VALUES ('2017-A2-3333 '); insert into ssn_test VALUES ('2017-22-33339'); insert into ssn_test VALUES ('2017-2-23333 '); insert into ssn_test VALUES ('1970-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

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 ('1970 Oracle Parkway, Redwood Shores, ca', '() {2,}', '') RESULT
2. FROM dual;

SELECT REGEXP_REPLACE ('1970 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 '\')

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 ('2017 Oracle Parkway, Redwood Shores, ca', ', [^,] +,') RESULT
2. FROM dual;

SELECT REGEXP_SUBSTR ('1970 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

1. 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 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;