Author: David Laurent

Oracle Regular Expressions:

Regular Expressions were first introduced in Oracle version 10g. Regular expressions enable us to search, validate, remove unwanted text and/or replace characters from the string. You can use it for any character data including CLOB and NCLOB as well.

Parameters of Oracle Regular Expressions:

The Parameters for regular expressions comprise of

• A source string.
• A Pattern that can be any string, pattern or text to be found in the source string. If your data type of pattern is different from that of source string then it will be converted to the data type of source string.
• Position that is a non zero positive value from which search should begin.
• Matching parameters which control the behavior of the search. These include

Removing unwanted characters from string:

You can use REGEXP_REPLACE function to remove unwanted characters from the string. It takes source string, string to be replaced and the string that will replace it. If you want to replace the specified pattern with nothing then write ‘' or NULL.

SQL> SELECT REPLACE('A-BCD-EFGH-IJK-LM-NOP','-',”) MYCOL FROM DUAL;

SQL> SELECT REPLACE('A-BCD-EFGH-IJK-LM-NOP','-',NULL) MYCOL FROM DUAL;

                        MYCOL
        ———————————
          ABCDEFGHIJKLMNOP

Finding number of occurrences of a pattern:

You can use REGEXP_COUNT function to obtain number of occurrences of pattern in the source string. It reduces the overhead of writing long PL/SQL code.

Below regular expression can be used to count number of occurrences of character ‘i' in the source string. We are ignoring case in matching parameter.

SQL> SELECT REGEXP_COUNT('This is my String','i',1,'i') FROM DUAL;

      REGEXP_COUNT('THISISMYSTRING','I',1,'I')
      —————————————————————
                                    3

Retrieving URL:

Below regular expression searches for pattern http:// followed by a substring of one or more alphanumeric characters and optionally, a period ( . ). At minimum 3 and at maximum 4 occurrences of this substring between http:// and either a slash ( / ) or the end of the string are searched.

SELECT

REGEXP_SUBSTR('http://www.mysite.com/support',

'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"

FROM DUAL;

              REGEXP_SUBSTR
          ———————————
          http://www.mysite.com/

Returning position of a string that matches specified pattern:

Below example looks for occurrences of words beginning with s , r , or p (regardless of case) followed by any six alphabetic characters. Oracle begins searching at the third character in the string and returns the position in the string of the character following the second occurrence of a seven-letter word beginning with s , r , or p , regardless of case.

SELECT REGEXP_INSTR( '500 Oracle Parkway , Redwood Shores , CA ', '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR" FROM DUAL;

In above regular expression

• '500 Oracle Parkway , Redwood Shores , CA ' is our source string,
• s|r|p means we have to look for s or r or p character.
• [:alpha:] means alphabetic characters
• {6} means match exactly 6 times
• 3 means start from 3 rd character
• 2 returns the position in the string of the character following the second occurrence
• 1 is the return_option
• i means ignore case-sensitivity

              REGEXP_INSTR
       ———————————
                     28

Validating Email Address:

Below query validates the email addresses. It requires three distinct pieces of an email address and allows for periods '.' as well as underscores '_' in the name.

SELECT email FROM MYCOL
WHERE REGEXP_LIKE (email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}');

Now if you want to search only email addresses that end with '.com' then you can re-write the above query as follows.

SELECT email FROM MYCOL WHERE REGEXP_LIKE (email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.com');

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle – SQL: Performance Boost with Collections
   Performing Oracle Automated Tablespace Point-in-Time Recovery!!
   Performance Optimization by avoiding Table Locks!!
   The Power of Oracle Standby Database with a Time Lag!!