Pattern Matching / REGEX Functions

Usage: REGEXP_COUNT(string, pattern, position)

Searches a string for a regular expression pattern and returns an integer that indicates the number of times the pattern occurs in the string.

If no match is found, then the function returns 0. The search starts at position 1 unless a value for position is provided.

Usage: REGEXP_INSTR(string, pattern, position, which occurrence, first or last, case sensitivity)

Searches a string for a regular expression pattern and returns an integer that indicates the beginning position or ending position of the matched substring.

If no match is found, then the function returns 0. Use the position argument to specify which position to begin searching (defaults to 1).

Use the which occurence argument to specify which found occurence to return results for (default is 1).

Use the first or last argument to specify whether the position returned is the first position of the found pattern (specify 0), or the last position (specify 1). Default is 0 to return the first position found.

Set the case sensitivity parameter to the string 'c' (the default) for a case-sensitive match or 'i' for case insensitive.

Usage: REGEXP_REPLACE(string, pattern, replacestring, position)

Searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified replacestring.

The search starts at position 1 unless a value for position is provided.

The replacestring is an empty string by default.

Usage: REGEXP_SUBSTR(string, pattern, position, which occurrence, case sensitivity)

Returns a substring of the provided string based on searching for a regular expression pattern.

Use the position argument to specify which position to begin searching (defaults to 1).

Use the which occurence argument to specify which found occurence to return results for (default is 1).

Set the case sensitivity parameter to the string 'c' (the default) for a case-sensitive match or 'i' for case insensitive.

Usage: string [NOT] LIKE pattern [ESCAPE 'escape_char'] (note, use ILIKE instead of LIKE for case-insensitive matching)

This performs simple pattern matching. It compares a string expression to a pattern, returning a 1 if the pattern was found, and a 0 otherwise.

If either string or pattern is NULL, the result is NULL. The pattern need not be a literal string, for example, it can be a string variable.

The pattern can include the % character to match any number of characters (a wildcard character), or the _ (underscore) character to match exactly one character.

Usage: string ~ pattern or string !~ pattern

This performs complex pattern matching using regular expression patterns.

It compares a string expression to the provided pattern, returning true if a match was found, and false otherwise.

Use within a CASE/WHEN sequence to handle the true or false result as you want.