Author: Bob Watkins

Page: 1 2

Oracle supports POSIX regular expressions for matching patterns in text strings. Oracle 10g Release 2 has extended the initial list of pattern metacharacters, with additional ones found in Perl.

Regular Expressions in Oracle:

The function REGEXP_LIKE searches a character column for a pattern

REGEXP_LIKE returns a Boolean TRUE or FALSE.

REGEXP_LIKE can be used in the WHERE clause in SQL SELECT statements to limit rows.

REGEXP_LIKE can be used in the PL/SQL blocks to check the validity of data.

Functions like REGEXP_REPLACE, REGEXP_SUBSTR, and REGEXP_INSTR can accept POSIX regular expressions in performing other familiar string operations.

Abbreviated Patterns:

Perl adds some abbreviated patterns for the POSIX "class" expressions.

In standard POSIX regular expressions, you use the pattern '[[:digit:]]' to represent a single digit from 0-9. In Perl, this is simplified to '\d'.

Curly braces indicate a repeat count, so '\d{3}' specifies three digits.

To indicate that you want a character that is anything except a digit, the POSIX pattern is '[^[:digit:]]'. But in Perl, it's just '\D'.

Similar shortcuts exist for whitespace: '\s' versus '[[:space:]]' and non-whitespace: '\S' versus '[^[:space:]]'.

SQL> SELECT last_name
  2  FROM employees
  3  WHERE REGEXP_LIKE(last_name, '[[:space:]]');

LAST_NAME  
-------------------------
De Haan  

SQL> SELECT last_name
  2  FROM employees
  3  WHERE REGEXP_LIKE (last_name, '\s');

LAST_NAME 
-------------------------
De Haan

SQL> SELECT last_name
  2  FROM employees
  3  WHERE REGEXP_LIKE (last_name, '\S')
  4  AND ROWNUM < 11;

LAST_NAME    
-------------------------
Abel
Ande   
Atkinson    
Austin
Baer 
Baida 
Banda  
Bates 
Bell  
Bernstein  
10 rows selected.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Effectively Prevent Oracle Minimum Downtime Migration Error!!
   Saving and Restoring Old Statistics in Oracle!!
   Great Tips for Test Data Generation in Oracle!!
   DBA Tips for Verifying Oracle Data Replication!!