3 Using Regular Expressions in Database Applications

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 through these 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, this 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 an alternative 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 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 Database support of regular expressions enables developers to implement complex match logic in the database. This technique is useful for these 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 DNA and proteins. Previously, finding a match for a protein sequence such as [AG].{4}GK[ST] was handled in the middle tier. The SQL regular expression functions move the processing logic closer to the data, thereby providing a more efficient solution.

• Before 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 3-1 describes 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')

REGEXP_COUNT

Function

REGEXP_COUNT('Albert Einstein', 'e', 7, 'c')

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 quotation marks. If your regular expression includes the single quotation mark, 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 can delimit your regular expression with the pound sign (#) and then use a single quotation mark within the expression.

Oracle Database Support for the POSIX Regular Expression Standard

Oracle Database implementation of regular expressions conforms to these 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 this URL:

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

Oracle Database enhances regular expression support in these 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 heavily used PERL regular expression operators, for example, character class shortcuts, the "nongreedy" modifier, and so on.

Oracle Database supports a set of common metacharacters used in regular expressions. For information about the action of supported metacharacters and related features, see "Metacharacters in Regular Expressions".

POSIX Metacharacters in Oracle Database Regular Expressions

Table 3-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 action from the standard are noted in the "Description" column.

Multilingual Extensions to POSIX Regular Expression Standard

When applied to multilingual data, Oracle Database implementation of the POSIX operators extends beyond the matching capabilities specified in the POSIX standard. Table 3-3 shows the relationship of the operators in 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 Database evaluates the characters based on the byte values used to encode the character, not the graphical representation of the character.

PERL-Influenced Extensions to POSIX Regular Expression Standard

Table 3-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 from the popularity of PERL. 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 3-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 a Constraint to Enforce a Phone Number Format

Regular expressions are useful for enforcing constraints. For example, suppose that you want to ensure that phone numbers are entered into the database in a standard format. Example 3-1 creates a contacts table and adds a CHECK constraint to the p_number column to enforce this format mask:

(XXX) XXX-XXXX

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

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

INSERT INTO contacts (p_number) VALUES('(650) 555-0100');
INSERT INTO contacts (p_number) VALUES('(215) 555-0100');
 

INSERT INTO contacts (p_number) VALUES('650 555-0100');
INSERT INTO contacts (p_number) VALUES('650 555 0100');
INSERT INTO contacts (p_number) VALUES('650-555-0100');
INSERT INTO contacts (p_number) VALUES('(650)555-0100');
INSERT INTO contacts (p_number) VALUES(' (650) 555-0100');

Using Back References to Reposition Characters

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

Example 3-3 creates a table, populates it with names in different formats, and uses a query that repositions names that are in the format "first middle last" to the format "last, first middle". It ignores names not in the format "first middle last". The elements of the regular expression in the query are explained in Table 3-7.

DROP TABLE famous_people;
CREATE TABLE famous_people (names VARCHAR2(20));
INSERT INTO famous_people (names) VALUES ('John Quincy Adams');
INSERT INTO famous_people (names) VALUES ('Harry S. Truman');
INSERT INTO famous_people (names) VALUES ('John Adams');
INSERT INTO famous_people (names) VALUES (' John Quincy Adams');
INSERT INTO famous_people (names) VALUES ('John_Quincy_Adams');

COLUMN "names after regexp" FORMAT A20

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

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
 
5 rows selected.