SQL REGEXP_LIKE() Function

SQL REGEXP_LIKE() function same as like condition but matching regular expression pattern to perform like condition.

SQL REGEXP_LIKE() function supported Oracle SQL version

  • Oracle 11g
  • Oracle 12c
  • Oracle 18c

Syntax

REGEXP_LIKE(original_string, pattern [ , match_param ] )

Parameters

  • original_string is a string which we want to represent in regular expression pattern.
  • pattern is a regular expression pattern.
  • match_param is a expression flag.
    • i - ignore case
    • c - case sensitive
    • n - match any character as well as match newline character
    • m - multi line
    • x - ignore whitespace

Example

Consider following example is REGEXP_LIKE function fetching 'Opa?l' regular expression pattern from the name.

SQL> SELECT employee_name
    FROM emp_info
    WHERE REGEXP_LIKE (name, 'Opa?l', 'im');

employee_name
-------------
         Opal
          Opl

Regular Expression References

Following are regular expressions operator that are create patterns for letter use either string replacing or getting sub string from the string using regular expression pattern.

Flags Reference

You can also specify optional regular expression flags. Flags that allow for global searching, case insensitive searching. These flags you can define either separately or together.

Flags Description
iignore case
ccase sensitive
nmatch any character as well as match newline character
mmulti line
xignore whitespace

Quantifiers/Alternative Classes

Character Description
.Any character except newline
Example. . - Matches any character
*Matches O or more preceding character
Example. b* - bbbeee
+Matches 1 or more preceding character
Example. b+ - bbbeee, beee
?Matches either 0 or 1 preceding character, effectively matches is optional
Example. Goog?le - Goole , Google
|Represent like a boolean OR for alternative matches
Example. AB|CD - match ab or cd

Grouping Classes

Character Description
[ ]Matches any character in the set
Example. [ABC] - matches any of a, b, or c
( )Capture groups of sequence character together
Example. (name) - matches sequence of group character

Ranging Classes

Character Description
{a}matches exactly m time
Example. b{1} - match exactly 1 time
{a,}matches exactly m or more time
Example. b{1,} - match exactly 1 or more time
{a, z}matches m to n times
Example. b{3,5} - match between 3 & 5

Escape Character Classes

Character Description
\specified the next special character
Example. \\ - Matches a "\" character.
\nMatches a n number (digit 1 to 10) LINE FEED character enclosed between ( and ).

Anchors Classes

Character Description
^Beginning of the string. If more then one line matches any beginning line.
Example. ^ABC - starting character A then match ABC
$Ending of the string. If more then one line matches any ending line. Example.
ABC$ - ending character C then match ABC
\AMatches only at the beginning of the string.
Example. h\A - hello Opal! (matches only 'hello')
\ZMatches only at the ending of the string.
Example. o\A - hello Opal! (matches only 'hello')

Character Classes

Character Description
\dMatches digit character
Example. \d - Hello123 (matches only '123')
\DMatches non digit character
Example. \d - Hello123 (matches only 'Hello')
\wMatches word character
Example. \w - Hello123###/* (matches only 'Hello123')
\WMatches non word character
Example. \W - Hello123###/* (matches only '###/*')
\sMatches whitespace
Example. \s - Hello 123 ### (matches only whitespace)
\SMatches non whitespace
Example. \S - Hello 123 ### (matches non whitespace 'Hello' and '123' and '###')