Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Using Regular Expression in Oracle

Table of Contents

Using Regular Expression in Oracle Database

What are Regular Expressions?

Regular expressions help us to search a specific pattern in a string. The pattern can be Numeric, Char or Alpha-numeric.

Does Oracle support regex?

Oracle allows following types of characters to specify Regular Expression:

  • Metacharacters
  • Literals

Implementation of Regular Expression:

Oracle Database SQL Functions for Regular Expressions:

Oracle provides a group of SQL functions and conditions that support Regular Expression. These SQL functions help us to search and manipulating string data.

Function/Condition Category Description
REGEXP_LIKEConditionWhat is the use of REGEXP_LIKE?
It searches a character columns for a pattern. It can be used in the WHERE clause of query to get the matching rows of a regular expression.
REGEXP_REPLACEFunction What is the use of REGEXP_REPLACE ?
It search for a pattern in a character column and replace it with the value of the string specified.
REGEXP_INSTRFunction What is the use of REGEXP_INSTR ?
It searches a string for the given occurrence of a regular expression pattern and returns an integer, indicating the position where the match was found.
REGEXP_SUBSTRFunction What is the use of REGEXP_SUBSTR ?
It searches a regular expression pattern and returns a substring that matches the pattern.

Regular Expression Metacharacters in Oracle Database:

Metacharacters are operators that specify search algorithm.

Metacharacters supported in Regular Expressions:

Oracle database allows following types of Metacharacters:

  • POSIX Metacharacters
  • Regular Expression Operator for Multilingual Enhancement
  • Perl Influenced Extensions in Oracle Regular Expressions
POSIX Metacharacters in Oracle Database Regular Expression:
Syntax Operator Name Description
.Match Any Character – Dot operatorIt matches any character in the given character set except NULL and new line character
+One or more – Plus quantifierIt matches one or more occurrences of the preceding subexpression
?Zero or One – Question Mark quantifierIt matches zero or one occurrences of the preceding subexpression
*Zero or more – Star quantifierIt matches zero or more occurrences of the preceding subexpression
{m}Interval – Exact CountIt matches exactly m occurrences of the preceding subexpression
{m, }Interval – At least CountIt matches at least m occurrences of the preceding subexpression
{m, n}Interval – Between CountIt matches at least m, and not more than n occurrences of the preceding subexpression
[ … ]Matching Character ListIt matches any single character in the list within the bracket
[^ … ]Non matching Character ListIt matches any single character not in the list within the bracket
|OrIt matches any of the alternatives
( … )Subexpression or GroupingTreats the operator within the bracket as a unit. The subexpression can be a string of literals or a complex expression containing operators
\nBackreferenceIt matches the nth precedence subexpression what is grouped within parentheses, where n is an integer between 1 and 9.
\Escape CharacterIt treats the subsequent metacharacter as a literal. Use a backslash ( \ ) to search for a character that is normally treated as a metacharacter.
^Beginning of Line Anchor It matches the beginning of a string. For multiline cases it matches the beginning of any line within the source string
$End of Line AnchorIt matches the end of a string
[:class:]POSIX character ClassMatches any character belonging to the specified POSIX character class
[.element.]POSIX Collating Element OperatorSpecifies a collating element to use in the regular expression
[=character=]POSIX Character Equivalence ClassIt matches all characters that are members of same character equivalence class

Example of POSIX Metacharacters:

Match Any Characters — DOT Operator (.) :
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'S');
Using Regular Expression in Oracle Database
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'S.l');
Using Regular Expression in Oracle: Example Output
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'S..l');
Using Regular Expression in Oracle: Example Output
Using Regular Expression in Oracle: Example Output
One or More — Plus Operator (+) :
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'll+');
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'lly+');
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'ss+');
Zero or One — Question Mark Operator (?):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'kk');
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'kk?');
Using Regular Expression in Oracle: Example Output

In the above example, ? is placed after the 2nd k, so the query returns LAST_NAME where there is at least one or zero k followed by first k.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'key');
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'key?');
Using Regular Expression in Oracle: Example Output

In the above example, ? is placed after y, so the query returns the LAST_NAME where there is at least one or zero y followed by ke.

Zero or More — Star Quantifier (*):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 're*n');
Using Regular Expression in Oracle: Example Output

In the above example, the data set returns are, r preceded by zero or more e, preceded by n.

Inserting one row in EMPLOYEES table where LAST_NAME contains 3 z:

Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (207,'William','Gietzzz','WGIETZZZ','515.123.8181',to_date('07-06-02','DD-MM-RR'),'AC_ACCOUNT',8300,null,205,110);
Interval — Exact Count ({m}):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'e{2}');
Using Regular Expression in Oracle: Example Output

In the above example, data set returns containing exactly 2 no of e.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'z{3}');
Using Regular Expression in Oracle: Example Output

In the above example data containing exactly 3 z.

Interval — At Least Count ({m, }):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'k{1,}');
Using Regular Expression in Oracle: Example Output

In the above example, the data set returns containing at least one k.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'z{3,}');

In the above example data containing at least 3 z.

Interval — Between Count ({m, n}):

Inserting one row in EMPLOYEES table where LAST_NAME contains 3 z:

Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (207,'William','Gietzzz','WGIETZZZ','515.123.8181',to_date('07-06-02','DD-MM-RR'),'AC_ACCOUNT',8300,null,205,110);

Querying data containing z between 1 and 3.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'z{1,3}');
Using Regular Expression in Oracle: Example Output
Matching Character List ([…]):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '[avi]');
Using Regular Expression in Oracle: Example Output

The above example returns the data set containing any single character of the group [avi] or all.

Non Matching Character List ([^…]):
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
AND REGEXP_LIKE(LAST_NAME, '[^H]a');
Using Regular Expression in Oracle: Example Output

In the above example data showing for DEPATMENT_ID 20, where LAST_NAME which is not started with H and followed by a.

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
AND REGEXP_LIKE(LAST_NAME, '[^F]a');
Using Regular Expression in Oracle: Example Output

In the above example data showing for DEPATMENT_ID 20, where LAST_NAME which is not started with F and followed by a.

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
AND REGEXP_LIKE(LAST_NAME, '[^HF]a');

In the above example data showing for DEPATMENT_ID 20, where LAST_NAME which is not started with F or H and followed by a. No such data is available in this case.

Or (|):
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
AND REGEXP_LIKE(LAST_NAME, '[R|B]a');
Using Regular Expression in Oracle: Example Output

In the above example data showing LAST_NAME starts with either Ra or Ba.

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
AND REGEXP_LIKE(LAST_NAME, '[T|Kh|C]o');
Using Regular Expression in Oracle: Example Output

In the above example, query returns data, where LAST_NAME starts with either To, Kho or Co.

Subexpression or Grouping ( … ) :
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
Using Regular Expression in Oracle: Example Output
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60
AND REGEXP_LIKE(LAST_NAME, '(st)');
Using Regular Expression in Oracle: Example Output

In the above example data set returns that contains st in the LAST_NAME.

Backreference(\n):
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
Using Regular Expression in Oracle: Example Output
SELECT REGEXP_REPLACE(A.PHONE_NUMBER, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1 \2 - \3)') REGEX_PHONE_NUMBER, A.*
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID = 60;
Using Regular Expression in Oracle: Example Output

In the above data set, new format has been provided to PHONE_NUMBER column with backreference operator.

Character (\):
Using Regular Expression in Oracle: Example Output
SELECT REGEXP_REPLACE(A.JOB_ID, '\_', '-') REGEX_JOBID,
       A.*
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID = 60;
Using Regular Expression in Oracle: Example Output

In the above data set JOB_ID column, '_' has been replaced by '-' with Escape character.

Beginning of Line Anchor (^):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '^Pat');
Using Regular Expression in Oracle: Example Output

In the above example data set returns those data where LAST_NAME starts with Pat.

End of Line Anchor ($):
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, 'an$');
Using Regular Expression in Oracle: Example Output

In the above example data returns those data where LAST_NAME ends with an.

POSIX character Class ( [:class:] ):
UPDATE EMPLOYEES
SET EMAIL = EMAIL||'@123'
WHERE EMPLOYEE_ID IN (104, 105);

2 rows updated.
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (104, 105);
Using Regular Expression in Oracle: Example Output
SELECT EMPLOYEE_ID, 
       LAST_NAME,
       EMAIL,
       REGEXP_SUBSTR(EMAIL, '[[:digit:]]{1}') DIGIT1,
       REGEXP_SUBSTR(EMAIL, '[[:digit:]]{2}') DIGIT2,
       REGEXP_SUBSTR(EMAIL, '[[:digit:]]{3}') DIGIT3,
       REGEXP_SUBSTR(EMAIL, '[[:digit:]]{4}') DIGIT4
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (104, 105);
Using Regular Expression in Oracle: Example Output

In the above example the data set returns the data which contains digit in EMAIL column. If you provide no of digit more than the digit available then null will be returned. Refer DIGIT4 column.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '[[:blank:]]');
Using Regular Expression in Oracle: Example Output

In the above example the data returns where LAST_NAME contains blank space.

UPDATE EMPLOYEES
SET EMAIL = 'berNST@123'
WHERE EMPLOYEE_ID = 104;

1 row updated.
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(EMAIL, '[[:lower:]]');
Using Regular Expression in Oracle: Example Output

In the above example the data returns where EMAIL contains lowercase letters.

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '[[:upper:]]{2}');
Using Regular Expression in Oracle: Example Output

In the above example the returns where 2 consecutive upper case letters exists in LAST_NAME column.

SELECT EMPLOYEE_ID, 
       LAST_NAME,
       EMAIL,
       REGEXP_SUBSTR(LAST_NAME, '[[:upper:]]') UPPER,
       REGEXP_SUBSTR(LAST_NAME, '[[:upper:]]', 2) UPPER2
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (104, 105);
Using Regular Expression in Oracle: Example Output

In the above example the data returns 1st and 2nd upper letter exists in LAST_NAME column of EMPLOYEE_ID 104 and 105. There is no second upper case letter exists in LAST_NAME Austin, so it returns null.

CREATE TABLE EMPLOYEES_TEST
AS
SELECT * FROM EMPLOYEES;

ALTER TABLE EMPLOYEES_TEST
ADD (REMARKS VARCHAR2(4000));
UPDATE EMPLOYEES_TEST
SET REMARKS = 'Zipcode of '||LAST_NAME||' is '||REGEXP_SUBSTR(PHONE_NUMBER, '[[:digit:]]+', 9);
SELECT *
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example the REMARKS column is update with the string Zipcode of LAST_NAME is last 4 digit of PHONE_NUMBER column.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(REMARKS, '[[:digit:]]{4}') ZIPCODE
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example digit characters were returned from REMARKS column as ZIPCODE.

POSIX Collating Element Operator ( [.element.] ) :
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[.kz.]');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains either k or z or both.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[.z.]');

Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains at least one z.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[aeiou]{3}');

In the above example the data set returns data where LAST_NAME contains consecutive 3 vowels.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[aeiou]{2}');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains consecutive 2 vowels.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(JOB_ID, '^[F-I]');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where JOB_ID starts with characters from F to I.

POSIX Character Equivalence Class ( [=character=] ):
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[[=f=]]');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains either lower or upper F.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       JOB_ID
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '[[=z=]]');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains either lower or upper Z.

Regular Expression Operator Multilingual Enhancements:

When Oracle REGEXP applied to multilingual data then POSIX operator extends beyond the matching capabilities of POSIX standard.

Operator Syntax Operator Name
^Beginning of Line Anchor
$End of Line Anchor
.Any Character — Dot
\nBackreference
[ .. ]Matching Character List
[ : : ]POSIX Character Class
[ == ]POSIX Character Equivalence Class

Perl-Influenced Extensions in Oracle Regular Expressions:

Below table lists the Perl-influenced metacharacters supported in Oracle. These operators are not in the POSIX standard but are supported due to the popularity in PERL.

Operator Syntax Operator Name Operator Description
\dA digit characterIt is equivalent to POSIX Class [[:digit:]]
\DA non-digit character It is equivalent to POSIX Class [^[:digit:]]
\wA word character identified as alphanumeric or underscore characterit is equivalent to POSIX class [[:alnum]_]
\WA nonword characterIt is equivalent to POSIX class [[:alnum:]_]
\sA whitespace characterIt is equivalent to POSIX class [[:space:]]
\SA non-whitespace characterIt is equivalent to POSIX class [^[:space]]
\AOnly at the beginning of a stringIn multiline mode that is when embedded new line characters in a string are considered the termination of a line \A does not match the beginning of each line.
\ZOnly at the end of a string or a newline ending a string. In multiline mode that is when embedded new line characters in a string are considered the termination of a line \Z does not match the end of each line.
\z Only at the end of a string Its the absolute end of a string
*?The preceding pattern element 0 or more timesThis quantifier matches the empty string whenever possible.
+?The preceding pattern element 1 or more times
?? The preceding pattern element 0 or 1 time
{n}?The preceding pattern element exactly n timesThis is equivalent to POSIX class {n}
{n, }? The preceding pattern element at least n times This is equivalent to POSIX class {n,}
{n,m}?At least n but not more than m times This is equivalent to POSIX class {n,m}
A digit character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(REMARKS, '\d{4}') ZIPCODE
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output
A non-digit character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       EMAIL, 
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(REMARKS, '\D{4}') DESCRIPTION,
       REGEXP_SUBSTR(REMARKS, '\D+') DESCRIPTION1
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example DESCRIPTION and DESCRIPTION1 columns return the non-digit characters of REMARKS column.

A word character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(REMARKS, '\w+', 1, 1) DESCRIPTION,
       REGEXP_SUBSTR(REMARKS, '\w+', 1, 2) DESCRIPTION1,
       REGEXP_SUBSTR(REMARKS, '\w+', 1, 3) DESCRIPTION2,
       REGEXP_SUBSTR(REMARKS, '\w+', 1, 4) DESCRIPTION3,
       REGEXP_SUBSTR(REMARKS, '\w+', 1, 5) DESCRIPTION4
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example the REMARKS column's individual words are extracted from the REMARKS column.

A nonword character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(PHONE_NUMBER, '\W', 1, 1) DESCRIPTION,
       REGEXP_SUBSTR(REMARKS, '\W', 1, 1) DESCRIPTION
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example DESCRIPTION columns returns the non-word characters like dot(.) and blank space.

A whitespace character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(PHONE_NUMBER, '\s', 1, 1) DESCRIPTION,
       REGEXP_SUBSTR(REMARKS, '\s', 1, 1) DESCRIPTION1
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example there is no white space in PHONE_NUMBER column so its returned null, while the REMARKS column contains spaces.

A non-whitespace character:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS,
       REGEXP_SUBSTR(LAST_NAME, '\S+', 1, 1) DESCRIPTION,
       REGEXP_SUBSTR(REMARKS, '\S+', 1, 1) DESCRIPTION1
FROM EMPLOYEES_TEST;
Using Regular Expression in Oracle: Example Output

In the above example LAST_NAME De Haan has a space within the name, so it returns the De only, while from the REMARKS column its returned the 1st word before the 1st space.

Only at the beginning of a string :
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, '\AL+');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns all the LAST_NAME start with L.

Only at the end of a string:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'a\Z+');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns all the LAST_NAME ends with a.

The preceding pattern element 0 or more times:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'old*?');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns the data where LAST_NAME contains complete or partial part of old.

The preceding pattern element 1 or more times:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'old+?');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns the LAST_NAME contains one or more occurrences of string old.

The preceding pattern element 0 or 1 time:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'nson??');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns the data where LAST_NAME contains nson.

The preceding pattern element exactly n times:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 's{2}?');
Using Regular Expression in Oracle: Example Output

In the above data set returns the data where LAST_NAME contains 2 consecutive s.

The preceding pattern element at least n times:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'z{1,}?');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains at least one z.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'z{3,}?');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns data where LAST_NAME contains at least 3 z.

At least n but not more than m times:
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,  
       PHONE_NUMBER,
       REMARKS
FROM EMPLOYEES_TEST
WHERE REGEXP_LIKE(LAST_NAME, 'z{1,3}?');
Using Regular Expression in Oracle: Example Output

In the above example the data set returns the data where LAST_NAME contains z between 1 and 3.

Pattern Matching Modifiers:

Modifiers Name Modifiers Description
iCase insensitive matching
cCase sensitive matching
nAllows the period(.), which by default does not match the new line, to match the new line character
mPerform the search in multiline mode, the metacharacter ^ and $ signify the start and end respectively, of any line any where in the source string, rather than start and end of entire source string.
xIgnores white space characters in the regular expression

RELATED TOPICS:

Leave a Comment

Your email address will not be published.