4 Using Regular Expressions in Oracle Database

What Are Regular Expressions?

Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. You specify a regular expression by means of the following types of characters:

• Metacharacters, which are operators that specify search algorithms

• Literals, which are the characters for which you are searching

A regular expression can specify complex patterns of character sequences. For example, the following regular expression searches for the literals f or ht, the t literal, the p literal optionally followed by the s literal, and finally the colon (:) literal:

(f|ht)tps?:

The parentheses are metacharacters that group a series of pattern elements to a single element; the pipe symbol (|) matches one of the alternatives in the group. The question mark (?) is a metacharacter indicating that the preceding pattern, in this case the s character, is optional. Thus, the preceding regular expression matches the http:, https:, ftp:, and ftps: strings.

How Are Oracle Database Regular Expressions Useful?

Regular expressions are a powerful text processing component of programming languages such as Perl and Java. For example, a Perl script can process each HTML file in a directory, read its contents into a scalar variable as a single string, and then use regular expressions to search for URLs in the string. One reason that many developers write in Perl is for its robust pattern matching functionality.

Oracle's support of regular expressions enables developers to implement complex match logic in the database. This technique is useful for the following reasons:

• By centralizing match logic in Oracle Database, you avoid intensive string processing of SQL results sets by middle-tier applications. For example, life science customers often rely on Perl to do pattern analysis on bioinformatics data stored in huge databases of DNAs and proteins. Previously, finding a match for a protein sequence such as [AG].{4}GK[ST] would be handled in the middle tier. The SQL regular expression functions move the processing logic closer to the data, thereby providing a more efficient solution.

• Prior to Oracle Database 10g, developers often coded data validation logic on the client, requiring the same validation logic to be duplicated for multiple clients. Using server-side regular expressions to enforce constraints solves this problem.

• The built-in SQL and PL/SQL regular expression functions and conditions make string manipulations more powerful and less cumbersome than in previous releases of Oracle Database.

Oracle Database Implementation of Regular Expressions

Oracle Database implements regular expression support with a set of Oracle Database SQL functions and conditions that enable you to search and manipulate string data. You can use these functions in any environment that supports Oracle Database SQL. You can use these functions on a text literal, bind variable, or any column that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2 (but not LONG).

Table 4-1 gives a brief description of the regular expression functions and conditions.

REGEXP_LIKE

Condition

WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')

REGEXP_REPLACE

Function

REGEXP_REPLACE(country_name, '(.)', '\1 ')

REGEXP_INSTR 

Function 

REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0

REGEXP_SUBSTR

Function

REGEXP_SUBSTR('oracle', 'o r a c l e', 1, 1, 'x')

A string literal in a REGEXP function or condition conforms to the rules of SQL text literals. By default, regular expressions must be enclosed in single quotes. If your regular expression includes the single quote character, then enter two single quotation marks to represent one single quotation mark within the expression. This technique ensures that the entire expression is interpreted by the SQL function and improves the readability of your code. You can also use the q-quote syntax to define your own character to terminate a text literal. For example, you could delimit your regular expression with the pound sign (#) and then use a single quote within the expression.

Oracle Database Support for the POSIX Regular Expression Standard

Oracle's implementation of regular expressions conforms to the following standards:

• IEEE Portable Operating System Interface (POSIX) standard draft 1003.2/D11.2

• Unicode Regular Expression Guidelines of the Unicode Consortium

Oracle Database follows the exact syntax and matching semantics for these operators as defined in the POSIX standard for matching ASCII (English language) data. You can find the POSIX standard draft at the following URL:

http://www.opengroup.org/onlinepubs/007908799/xbd/re.html

Oracle Database enhances regular expression support in the following ways:

• Extends the matching capabilities for multilingual data beyond what is specified in the POSIX standard.

• Adds support for the common Perl regular expression extensions that are not included in the POSIX standard but do not conflict with it. Oracle Database provides built-in support for some of the most heavily used Perl regular expression operators, for example, character class shortcuts, the non-greedy modifier, and so on.

Oracle Database supports a set of common metacharacters used in regular expressions. The behavior of supported metacharacters and related features is described in "Regular Expression Metacharacters in Oracle Database".

POSIX Metacharacters in Oracle Database Regular Expressions

Table 4-2 lists the list of metacharacters supported for use in regular expressions passed to SQL regular expression functions and conditions. These metacharacters conform to the POSIX standard; any differences in behavior from the standard are noted in the "Description" column.

Regular Expression Operator Multilingual Enhancements

When applied to multilingual data, Oracle's implementation of the POSIX operators extends beyond the matching capabilities specified in the POSIX standard. Table 4-3 shows the relationship of the operators in the context of the POSIX standard.

• The first column lists the supported operators.

• The second column indicates whether the POSIX standard for Basic Regular Expression (BRE) defines the operator.

• The third column indicates whether the POSIX standard for Extended Regular Expression (ERE) defines the operator.

• The fourth column indicates whether the Oracle Database implementation extends the operator's semantics for handling multilingual data.

Oracle Database lets you enter multibyte characters directly, if you have a direct input method, or use functions to compose the multibyte characters. You cannot use the Unicode hexadecimal encoding value of the form \xxxx. Oracle evaluates the characters based on the byte values used to encode the character, not the graphical representation of the character.

Perl-Influenced Extensions in Oracle Regular Expressions

Table 4-4 describes Perl-influenced metacharacters supported in Oracle Database regular expression functions and conditions. These metacharacters are not in the POSIX standard, but are common at least partly due to the popularity of Perl. Note that Perl character class matching is based on the locale model of the operating system, whereas Oracle Database regular expressions are based on the language-specific data of the database. In general, a regular expression involving locale data cannot be expected to produce the same results between Perl and Oracle Database.

The Oracle Database regular expression functions and conditions support the pattern matching modifiers described in Table 4-5.

REGEXP_SUBSTR('AbCd', 'abcd', 1, 1, 'i')

REGEXP_SUBSTR('AbCd', 'abcd', 1, 1, 'c')

REGEXP_SUBSTR('a'||CHR(10)||'d', 'a.d', 1, 1, 'n')

REGEXP_SUBSTR('ab'||CHR(10)||'ac', '^a.', 1, 2, 'm') 

REGEXP_SUBSTR('abcd', 'a b c d', 1, 1, 'x')

Using an Integrity Constraint to Enforce a Phone Number Format

Regular expressions are a useful way to enforce integrity constraints. For example, suppose that you want to ensure that phone numbers are entered into the database in a standard format. Example 4-1 creates a contacts table and adds a check constraint to the p_number column to enforce the following format mask:

(XXX) XXX-XXXX

CREATE TABLE contacts
(
  l_name    VARCHAR2(30), 
  p_number  VARCHAR2(30)
    CONSTRAINT p_number_format      
      CHECK ( REGEXP_LIKE ( p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
);

Table 4-6 explains the elements of the regular expression.

Example 4-2 shows a SQL script that attempts to insert seven phone numbers into the contacts table. Only the first two INSERT statements use a format that conforms to the p_number_format constraint; the remaining statements generate check constraint errors.

-- first two statements use valid phone number format
INSERT INTO contacts (p_number)
  VALUES(  '(650) 555-5555'   );
INSERT INTO contacts (p_number)
  VALUES(  '(215) 555-3427'   );
-- remaining statements generate check contraint errors
INSERT INTO contacts (p_number)
  VALUES(  '650 555-5555'     );
INSERT INTO contacts (p_number)
  VALUES(  '650 555 5555'     );
INSERT INTO contacts (p_number)
  VALUES(  '650-555-5555'     );
INSERT INTO contacts (p_number)
  VALUES(  '(650)555-5555'    );
INSERT INTO contacts (p_number)
  VALUES(  ' (650) 555-5555'  );
/

Using Back References to Reposition Characters

As explained in Table 4-2, back references store matched subexpressions in a temporary buffer, thereby enabling you to reposition characters. You access buffers with the \n notation, where \n is a number between 1 and 9. Each subexpression is contained in parentheses and is numbered from left to right.

Example 4-3 creates a famous_people table and populates the famous_people.names column with names in different formats.

CREATE TABLE famous_people
  ( names VARCHAR2(30) );
-- populate table with data
INSERT INTO famous_people
  VALUES ('John Quincy Adams');
INSERT INTO famous_people
  VALUES ('Harry S. Truman');
INSERT INTO famous_people
  VALUES ('John Adams');
INSERT INTO famous_people
  VALUES (' John Quincy Adams');
INSERT INTO famous_people
  VALUES ('John_Quincy_Adams');
COMMIT;

Example 4-4 shows a query that repositions names in the format "first middle last" to the format "last, first middle". It ignores names not in the format "first middle last".

SELECT names "names",
  REGEXP_REPLACE(names,
                 '^(\S+)\s(\S+)\s(\S+)$',
                 '\3, \1 \2')
  AS "names after regexp"
FROM famous_people;

Table 4-7 explains the elements of the regular expression.

Example 4-5 shows the result set of the query in Example 4-4. The regular expression matched only the first two rows.

names
------------------------------
names after regexp
------------------------------
John Quincy Adams
Adams, John Quincy

Harry S. Truman
Truman, Harry S.

John Adams
John Adams

 John Quincy Adams
 John Quincy Adams

John_Quincy_Adams
John_Quincy_Adams