• Piyush Ranjan

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

Oracle RegExp_Like Example to check source and pattern value

REGEXP_LIKE Example 2

This example looks for values where there are at least two consecutive e characters

Oracle RegExp_Like Example to 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

Oracle RegExp_Like Example to looks for values that have the letter “C” in them

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 Example to looks for values that have either a “C” or a “c” in it.

REGEXP_LIKE Example 5

This example shows results that have an uppercase V where c denotes the search is case-sensitive

Regexp_like example to shows results that have a “V”, but not those that have a lowercase “v”.

REGEXP_LIKE Example 6

This example shows values that contains digits

Reg_exp example shows values that have digits inside them

REGEXP_LIKE Example 7

This example shows values that have alphabetical characters

Regexp_Like 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 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 finds the position of a string that starts with either A, B, or C, and then has 4 alphabetical characters

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 finds the position of strings that have two vowels in a row.

REGEXP_INSTR Example 4

This example shows the position of values where there are two vowels in a row, after position 4

REG_EXP_INSTR 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 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

REGEXP_INSTR 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 removes all occurrences of two consecutive vowels.

REGEXP_REPLACE Example 2

This example replaces two consecutive vowels with two dashes

REGEP_REPLACE 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 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 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 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 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

REGEXP_REPLACE 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 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 xample 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 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 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 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

REGEXP_SUBSTR 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 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 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 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 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

REGEXP_COUNT 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’

54 views

Recent Posts

See All

Oracle database refresh is the most common activity in any environment. There are multiple ways to perform the database refresh. Here you will find most common methods that are used for refresh activi

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta