Oracle Regular Expression

Regular expressions specify patterns to search for in string data using standardized syntax conventions. A regular expression can specify complex patterns of character sequences. For example, the following regular expression:

a(b|c)d

searches for the pattern: 'a', followed by either 'b' or 'c', then followed by 'd'. This regular expression matches both 'abd' and 'acd'.

Oracle Database 11g offers five regular expression functions as below:

1. REGEXP_LIKE
2. REGEXP_SUBSTR
3. REGEXP_REPLACE
4. REGEXP_INSTR
5. REGEXP_COUNT

REGEXP_LIKE(source, regexp, modes) :

This function searches a character column for a pattern. 

source parameter - is the string or column the regex should be matched against. 

regexp parameter - is a string with the regular expression. 

modes parameter -  is optional. It sets the matching modes.

• In SQL, can be used in the WHERE and HAVING clauses of a SELECT statement to return rows matching the regular expression specified.  

          Example:

         SELECT * FROM emp 
     WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

     FIRST_NAME           LAST_NAME
     -------------------- -------------------------
     Steven               King
     Steven               Markle
     Stephen              Stiles

• In PL/SQL script, it returns a Boolean value. It can be used in Check Conditions.

       Example:

      IF REGEXP_LIKE('subject', 'regexp') 

      THEN 

          /* Match */ 

      ELSE 

          /* No match */ 

      END IF;

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) :

This function returns the actual substring matching the regular expression pattern specified. If the match attempt fails, NULL is returned. 

position parameter - specifies the character position in the source string at which the match attempt should start. The first character has position 1. 

occurrence parameter - specifies which match to get. Set it to 1 to get the first match. If you specify a higher number, Oracle will continue to attempt to match the regex starting at the end of the previous match, until it found as many matches as you specified. The last match is then returned. If there are fewer matches, NULL is returned. 

Example:

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,')"REGEXPR_SUBSTR"
  FROM DUAL;
REGEXPR_SUBSTR
-----------------
, Redwood Shores,

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) 

This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern specified.

Example:

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM emp;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes) 

This function searches a string for a given occurrence of a regular expression pattern. If we specify, which occurrence we want to find and the start position to search from, this function returns an integer indicating the position in the string where the match is found.

Example:

The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"
  FROM DUAL;
REGEXP_INSTR
------------
          37

REGEXP_COUNT(source, regexp, position, modes) 

This function returns the number of times the regex can be matched in the source string. It returns zero if the regex finds no matches at all. This function is only available in Oracle 11g and later.

Example:

SELECT REGEXP_COUNT(first_name, 'S', 1) FROM emp;

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical