Regular Expressions

Oracle, SQL Server, MySQL, and PostgreSQL support regular expressions (regexes). SQL Server and MySQL support them only for string comparison, whereas PostgreSQL adds support for a regular expression substring function, and Oracle provides support for that and much more. DB2 Version 8.2 does not support regular expressions at all.

Regular Expressions (Oracle)

Oracle Database 10g implements the following regular-expression functions:

REGEXP_INSTR(source_string, pattern
            [, position [, occurrence
            [, return_option
            [, match_parameter]]]])

REGEXP_LIKE (source_string, pattern
            [, match_parameter])

REGEXP_REPLACE(source_string, pattern
              [, replace_string
              [, position [, occurrence
              [, match_parameter]]]])

REGEXP_SUBSTR(source_string, pattern
             [, position [, occurrence
             [, match_parameter]]])

Parameters are as follows:

source_string

The string you wish to search.

pattern

A regular expression describing the text pattern you are searching for. This expression may not exceed 512 bytes in length.

replace_string

The replacement text. Each occurrence of pattern in source_string is replaced by replace_string, which can use backreferences to refer to values that match subexpressions in the pattern.

position

The character position at which to begin the search. This defaults to 1 and must be positive.

occurrence

The occurrence of pattern you are interested in finding. This defaults to 1. Specify 2 if you want to find the second occurrence of the pattern, 3 for the third occurrence, and so ...

Get SQL Pocket Guide, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial