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
   Disk I/O Tuning with Oracle RAID Stripe Size!!
   Adding Custom Messages to Oracle Alert Log!!
   Efficiently handle Tricky Data Guard Failures!!
   12 U’s for Good Database Design