Oracle REGEXP Functions
Explore advanced text matching with Oracle REGEXP functions.
The Oracle REGEXP_ functions are used to search and manipulate strings using simple string patterns. These functions can be used in SQL and PL/SQL statements. The expression is made up of special characters, which have their own meaning. This expression is then used in a regular expression function, and then the result is used in your query.
Why Use Regular Expressions?
There are several string operations in Oracle SQL that let you perform some comparisons. For example, you can use UPPER to find upper case values, and you can use LIKE along with the wildcard characters %, _ and * to find specific values. These functions are insufficient for more intricate inspections, though. Regular expressions can be used to:
Check phone number formats
Check email address formats
Check URLs match a specific format
Check any other type of string value to see if it matches the desired format
There are a few functions in Oracle SQL that can be used with regular expressions:
REGEXP_LIKE
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REGEXP_COUNT (added in Oracle 11g)
Oracle REGEXP_LIKE Function
The REGEXP_LIKE function looks for a certain pattern in a column. It is utilised in a WHERE clause to determine if the row should be included in the result set. The syntax for the REGEXP_LIKE function is
REGEXP_LIKE (source_string, pattern [, match_parameter] )Where:
source_string (mandatory): The value that is searched in. It can be any data type of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression that you provide. It can be up to 512 bytes.
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of below:
“i”: case-insensitive matching
“c”: case-sensitive matching
“n”: allows the “.” character to match the newline character instead of any character
“m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.
If you specify multiple match_parameter values that contradict each other (e.g. “ci” which matches to case-sensitive and case-insensitive), then Oracle uses the last value. In this example, it will use “i” instead of “c”.
If you don’t specify a match parameter, then:
The default case sensitivity is determined by the parameter NLS_SORT
The period character doesn’t match the newline character
The source string is treated as a single line and not multiple lines
REGEXP_LIKE Example 1
This example uses just a source and pattern value
select first_name from hr.employees where regexp_like(first_name,'W+');This will return values that start with W and have one or more characters following them
REGEXP_LIKE Example 2
This example looks for values where there are at least two consecutive e characters
REGEXP_LIKE Example 3
This example looks for values that have the letter C at any position in the string
REGEXP_LIKE Example 4
This example looks for values that have either a C or a c in it at any position where i denotes the search is case-insensitive
REGEXP_LIKE Example 5
This example shows results that have an uppercase V where c denotes the search is case-sensitive
REGEXP_LIKE Example 6
This example shows values that contains digits
REGEXP_LIKE Example 7
This example shows values that have alphabetical characters
Oracle REGEXP_INSTR Function
The Oracle REGEXP_INSTR function lets you search a string for a regular expression pattern, and returns a number that indicates where the pattern was found. The syntax for the REGEXP_INSTR function is
REGEXP_INSTR (
source_string, pattern [, position [, occurrence
[, return_option [, match_parameter [, sub_expression ] ] ] ] ])where:
source_string (mandatory): This is the character string that the expression is searched in. It can be any of CHAR, VARCHAR2, NCHAR, NVACHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression that is used to search within the source_string. It can be any of CHAR, VARCHAR, NCHAR, or NVARCHAR2, and can be up to 512 bytes.
position (optional): This is the position in the source_string where the function should begin the search for the pattern. It must be a positive integer, and the default value is 1 (the search begins at the first character).
occurrence (optional): This is a positive integer that indicates which occurrence of the pattern within the source_string the function should search for. The default value is 1, which means the function finds the first occurrence. If the value is greater than 1, then the function looks for the second occurrence (or further occurrences) after the first occurrence is found.
return_option (optional): This lets you specify what happens when an occurrence is found. If you specify 0, which is the default, the function returns the position of the first character of the occurrence. If you specify 1, then the function returns the position of the character after the occurrence.
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of:
“i”: case-insensitive matching
“c”: case-sensitive matching
“n”: allows the “.” character to match the newline character instead of any character
“m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.
sub_expression (optional): If the pattern has subexpressions, this value indicates which subexpression is used in the function. The default value is 0.
REGEXP_INSTR Example 1
This example finds the position of the ee within a string
REGEXP_INSTR Example 2
This example finds the position of a string that starts with either A, B, or C, and then has 4 alphabetical characters following it
REGEXP_INSTR Example 3
This example finds the position of strings that have two vowels in a row. The REGEXP_INSTR value is different for each row depending on where the two vowels start
REGEXP_INSTR Example 4
This example shows the position of values where there are two vowels in a row, after position 4
REGEXP_INSTR Example 5
This example shows the position of the second occurrence in a string where there is a vowel after position 5
REGEXP_INSTR Example 6
This example shows the position of values that have an A, B, C, D, or E, followed by a vowel, using a case-insensitive search
Oracle REGEXP_REPLACE Function
The Oracle REGEXP_REPLACE function is used to search a string for a regular expression and replace it with other characters. It’s an extension of the standard Oracle REPLACE function, but REPLACE does not support regular expressions where REGEXP_REPLACE does. The syntax for this function is
REGEXP_REPLACE (source_string, pattern
[, replace_string [, position [, occurrence [, match_parameter ] ] ] ])Where:
Source_string (mandatory): This is the string to be searched in for this function. It is usually a character value and can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression and is used to search within the source_string. It can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2, and can be up to 512 bytes.
replace_string (optional): This is a value that is used to replace the occurrences of the pattern within the source_string. It can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. This replace_string can contain backreferences to subexpressions in the pattern by using backslashes (\), which I will show you in the examples below.
position (optional): This is the position in the source_string where the function should begin the search for the pattern. It must be a positive integer, and the default value is 1 (the search begins at the first character).
occurrence (optional): This is a positive integer that indicates which occurrence of the pattern within the source_string the function should search for. The default value is 1, which means the function finds the first occurrence. If the value is greater than 1, then the function looks for the second occurrence (or further occurrences) after the first occurrence is found.
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of:
“i”: case-insensitive matching
“c”: case-sensitive matching
“n”: allows the “.” character to match the newline character instead of any character
“m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.
The return value is a VARCHAR2 if the source_string is not a CLOB or NCLOB, and CLOB if it is.
REGEXP_REPLACE Example 1
This example removes all occurrences of two consecutive vowels
REGEXP_REPLACE Example 2
This example replaces two consecutive vowels with two dashes
REGEXP_REPLACE Example 3
This example replaces two consecutive vowels of the same vowel with two dashes
REGEXP_REPLACE Example 4
This example replaces any digits in the string with a + and then the digit
REGEXP_REPLACE Example 5
This example replaces any vowel character followed by any letter from a to m, starting from position 4, with two dashes
REGEXP_REPLACE Example 6
This example replaces the second occurrence of any vowel character followed by any letter from a to m, starting from position 1, with two dashes
REGEXP_REPLACE Example 7
This example replaces more than one consecutive capitalised letter with an underscore, starting from position 2
Oracle REGEXP_SUBSTR Function
The Oracle REGEXP_SUBSTR function allows you to search for a string inside another string, using regular expressions. It’s similar to the REGEXP_INSTR function, but instead of returning the position of the string, it returns the substring. One of the uses is to split a string into separate rows.
It extends the SUBSTR function but allows the user of regular expressions. The function returns a VARCHAR2 or CLOB data type, depending on what has been provided as an input. The syntax of the REGEXP_SUBSTR function is
REGEXP_SUBSTR (source_string, pattern [, position [, occurrence [, match_parameter ] ] ])Where:
source_string (mandatory): This is the string to be searched inside of. It is usually the larger of the two parameters, and is usually a character value and can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression and is used to search within the source_string. It can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2, and can be up to 512 bytes.
position (optional): This is the position in the source_string where the function should begin the search for the pattern. It must be a positive integer, and the default value is 1 (the search begins at the first character).
occurrence (optional): This is a positive integer that indicates which occurrence of the pattern within the source_string the function should search for. The default value is 1, which means the function finds the first occurrence. If the value is greater than 1, then the function looks for the second occurrence (or further occurrences) after the first occurrence is found.
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of:
“i”: case-insensitive matching
“c”: case-sensitive matching
“n”: allows the “.” character to match the newline character instead of any character
“m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.
REGEXP_SUBSTR Example 1
This example finds a substring that matches two consecutive vowels
REGEXP_SUBSTR Example 2
This example finds all consecutive vowels in a string that are the same, and returns NULL for those that don’t have consecutive vowels that are the same
REGEXP_SUBSTR Example 3
This example finds substrings that contain one or more digits
REGEXP_SUBSTR Example 4
This example finds substrings that have a vowel followed by a letter from a to m, starting from position 4
REGEXP_SUBSTR Example 5
This example finds the second occurrence of substrings that have a vowel followed by a letter from a to m
REGEXP_SUBSTR Example 6
This example finds substrings that contain one or more consecutive capital letters starting from position 2
Oracle REGEXP_COUNT
The Oracle REGEXP_COUNT function finds the number of times a pattern occurs in a particular string. It returns an integer which indicates the number of times it was found. If no matches are found, it returns 0. The REGEXP_COUNT function is a new function in Oracle 11g. The syntax of this function is
REGEXP_COUNT (source_string, pattern [, position [, match_parameter ] ] ] )Where:
source_string (mandatory): This is the string to be searched inside of. It is usually a character value and can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression and is used to search within the source_string. It can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2, and can be up to 512 bytes.
position (optional): This is the position in the source_string where the function should begin the search for the pattern. It must be a positive integer, and the default value is 1 (the search begins at the first character).
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of:
“i”: case-insensitive matching
“c”: case-sensitive matching
“n”: allows the “.” character to match the newline character instead of any character
“m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.
“x”: ignores whitespace characters. By default, any whitespace characters will match.
The REGEXP_COUNT function ignores subexpressions in the pattern. This means any brackets used to create subexpression patterns are ignored and the pattern is treated as a single expression.
REGEXP_COUNT Example 1
This example finds the number of occurrences that contain two consecutive vowels
REGEXP_COUNT Example 2
This example finds the number of occurrences that contain vowels
REGEXP_COUNT Example 3
This example finds the number of occurrences that contain digits
REGEXP_COUNT Example 4
This example finds the number of occurrences that contain vowels, starting from position 3
REGEXP_COUNT Example 5
This example finds the number of occurrences that contain a capital letter, starting from position 2
Function Syntax Summary
As you may have noticed, the REGEXP functions have a lot of similar parameters. This table highlights how the parameters are the same between each function
Oracle Regular Expression Patterns
Regular expressions are a sequence of characters that are used to search another string. Each of the characters inside a regular expression has a specific meaning. There are characters called metacharacters, which are used to specify the rules used for searching a character or set of characters.
This table shows the metacharacters used in Oracle.
Let’s take a look at some examples of using regular expression patterns. If you provide a pattern of ‘abc’, with no metacharacters, you will find the sequence ‘abc’.
Pattern: ‘abc’
Matches: ‘abc’
Does not match: ‘abd’, ‘ab’
Match Any Single Character
The dot or period character ‘.’ will match any single character in your string. So, if you’re looking for an ‘ab’ followed by any single character, then a ‘d’, you will use a pattern of ‘ab.d’.
Pattern: ‘ab.d’
Matches: ‘abcd’, ‘abad’, ‘abbd’, ‘ab9d’
Does not match: ‘abd’, ‘ababd’
Match Any One or More Characters
The plus character ‘+’ will match any single or multiple occurrences of the previous expression or character. For example, to find one or more occurrences of the letter ‘e’, you would use the pattern ‘e+’.
Pattern: ‘e+’
Matches: ‘e’, ‘ee’, ‘eee’, ‘eeeeeeeee’
Does not match: ‘aaa’, ‘each’, ‘eaea’
Match Zero or One Character
The question mark character ‘?’ will match zero or one, and only one, occurrence of the previous expression or character. It’s helpful for specifying optional characters. For example, to find a character string ‘abcd’ where the ‘c’ may or may not be there, you would use the pattern ‘abc?d’.
Pattern: ‘abc?d’
Matches: ‘abcd’, ‘abd’
Does not match: ‘acd’, ‘cabd’
Match Zero or More Characters
The asterisk character ‘*’ allows you to match zero or more occurrences of the previous expression or character. For example, to find a character string that starts with ‘a’ and ends in ‘b’, you could use the pattern ‘a*b’.
Pattern: ‘a*b’
Matches: ‘acedb’, ‘ab’, ‘aeb’
Does not match: ‘abcd’, ‘ba ’, ‘abcde’
Match an Exact Number of Characters
The exact count operator for regular expressions is written as a single digit inside curly braces {}. This finds the exact number of occurrences of the preceding character or expression. For example, to find expressions matching ‘eeeeee’ you would use a pattern of ‘e{6}’.
Pattern: ‘e{6}’
Matches: ‘eeeeee’
Does not match: ‘eee’, ‘e6’
Match At Least a Number of Characters
The at least count operator for regular expressions is written as a single digit and a comma inside curly braces {}. This finds occurrences where at least the specified number of preceding characters or expressions are found. For example, to find if the character ‘d’ occurs at least 4 times, you would use a pattern of ‘d{4,}’.
Pattern: ‘d{4,}’
Matches: ‘dddd’, ‘ddddd’, ‘ddddddd’
Does not match: ‘ddd’, ‘d4’, ‘d’
Match a Number of Characters Between Two Counts
The between count operator for regular expressions can be used to find occurrences of a specified value within a range. This is done by specifying the lower and upper bounds of the range, separated by a comma, inside curly braces {}. For example, to find where the character ‘g’ occurs between 3 and 5 times, you would use a pattern of ‘g{3,5}’.
Pattern: ‘g{3,5}’
Matches: ‘ggg’, ‘gggg’, ‘ggggg’
Does not match: ‘gg’, ‘goooo’, ‘ggegge’
Match a Character List
To match a list of characters, you can specify a pattern of characters inside square brackets []. This will find any occurrences of any of the specified characters. For example, to find where either the ‘a’, ‘e’, or ‘o’ character exists, you would use a pattern of [aeo].
Pattern: [aeo]
Matches: ‘alpha’, ‘egg’, ‘orange’
Does not match: ‘dig’, ‘why’, ‘xbzpgws’
Non-Matching Character List
To match against a list of characters you don’t want to match, you can specify a non-matching list. This is done by specifying a ^ character inside a character list. Characters that do not match any of the characters in your square brackets are returned by the match. For example, to exclude strings with ‘m’ and ‘p’ in them from your results, use a pattern of [^mp].
Pattern: [^mp]
Matches: ‘mpera’, ‘wamplne’
Does not match: ‘mp’
Range Operator
To match on a range of characters, you can use the ‘-‘ symbol between two characters inside square brackets []. This will find occurrences of any character between the two characters. For example, to find values that contain any character between ‘a’ and ‘e’, you would use the pattern of [a-e].
Pattern: [a-e]
Matches: ‘alpha’, ‘end’ ‘bubble’
Does not match: ‘zip’, ‘mug’, ‘up’
OR Operator
To specify an alternative to a character or expression, you can use the pipe character ‘|’ as an OR expression. For example, to match the value ‘a’ or ‘e’, you can use the pattern of ‘a|e’.
Pattern: ‘a|e’
Matches: ‘a’, ‘e’
Does not match: ‘w’, ‘c’
Subexpression or Grouping
You can use subexpressions to group characters you want to find as a whole. You do this by enclosing them in round brackets (). For example, to find the optional value of ‘un’ followed by the characters ‘col’, you can use the pattern of ‘(un)?col’.
Pattern: ‘(un)?col’
Matches: ‘col’, ‘uncol’
Does not match: ‘un’, ‘ununcolcol’
Backreference
The backreference feature allows you to search for a repeated expression. This is done by specifying subexpressions, and then using a backslash ‘\’ and a number, where the number is from 1-9 and indicates the number of the subexpression. This will be explained further with examples later in this article. For example, to find repeated expressions of either ‘aa’ or ‘bb’, you can use a pattern of ‘(aa|bb)\1’.
Pattern: ‘(aa|bb)\1’
Matches: ‘aaaa’, ‘bbbb’
Does not match: ‘abab’, ‘aa’, ‘bb’
Escape Character
The escape character can be used to search for a character that is usually used for a meta character. For example, you can search of * or + characters by putting an escape character before this character, such as ‘\*’
Pattern: ‘\*’
Matches: ‘awe*po’
Does not match: ‘awepo’, ‘abc’
Beginning of Line Anchor
To find the occurrence of a string that only occurs at the beginning of a line, use the caret ‘^’ operator. For example, to find expressions where the letter ‘B’ appears at the beginning of a line, use a pattern of ‘^B’.
Pattern: ‘^B’
Matches: ‘Big’, ‘Beyond’
Does not match: ‘At the Big’, ‘ABC is easy’
End of Line Anchor
The end-of-line character of a dollar sign ‘$’ allows you to search for an expression that occurs only at the end of a line. For example, to find expressions where the letter ‘e’ occurs at the end of a line, use a pattern of ‘e$’.
Pattern: ‘e$’
Matches: ‘three’, ‘septe’
Does not match: ‘abcdef’, ‘fourteen’
POSIX Character Class
The POSIX Character Class operator lets you search for an expression that matches a POSIX character class. POSIX character classes are a standard set of values that represent other values. They are represented by a keyword enclosed in a colon ‘:’ and two sets of square braces [[]].
Here is the full list of POSIX character classes
To find at least four upper case characters, use a pattern of ‘[[:upper:]]{4,}’
Pattern: ‘[[:upper:]]{4,}’
Matches: ‘ABCDE’, ‘WHYEPAFdce’
Does not match: ‘AbCdEfGhIj’, ‘EPOfjnvkdfvnPO’
POSIX Collating Sequence
The POSIX collating sequence operator lets you specify a collating sequence in your regular expression. This operator is a value inside two dots ‘..‘ and double square braces [[]]. For example, to find the collating sequence of ‘ch’ in your strings, then you would use a value of ‘[[.ch.]]’.
Pattern: ‘[[.ch.]]’
Matches: ‘chabc’
Does not match: ‘cdefg’
POSIX Character Equivalence Class
The POSIX character equivalence class allows you to find characters in the current locale that are equivalent characters. This allows you to search for characters with and without accents, for example, depending on the locale. For example, to find strings that contain an equivalent of ‘e’, use the pattern of ‘[[=e=]]’.
Pattern: ‘[[=e=]]’
Matches: ‘west’, ‘café’
Does not match: ‘window’, garçon’
