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 |
|---|---|
| i | ignore case |
| c | case sensitive |
| n | match any character as well as match newline character |
| m | multi line |
| x | ignore 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. |
| \n | Matches 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 |
| \A | Matches only at the beginning of the string. Example. h\A - hello Opal! (matches only 'hello') |
| \Z | Matches only at the ending of the string. Example. o\A - hello Opal! (matches only 'hello') |
Character Classes
| Character | Description |
|---|---|
| \d | Matches digit character Example. \d - Hello123 (matches only '123') |
| \D | Matches non digit character Example. \d - Hello123 (matches only 'Hello') |
| \w | Matches word character Example. \w - Hello123###/* (matches only 'Hello123') |
| \W | Matches non word character Example. \W - Hello123###/* (matches only '###/*') |
| \s | Matches whitespace Example. \s - Hello 123 ### (matches only whitespace) |
| \S | Matches non whitespace Example. \S - Hello 123 ### (matches non whitespace 'Hello' and '123' and '###') |