Regular Expression In Oracle

REGULAR EXPRESSION:

            A regular expression is a string or set of characters that describes the structure of some text .This expression helps to search a particular pattern on the line. It is composed of Literals and Metacharacters.

REGEXP_LIKE:

            This is an operator similar to LIKE operator. Like operator allows simple pattern matching. For complex patterns, SQL query holds lot of OR and LIKE combination. This Regular expression operator handles complex pattern. It is used in the WHERE and HAVING clauses.

SYNTAX: REGEXP_LIKE (source_string, pattern, match_parameter)

source_string à represents the  string in which the pattern is searched

pattern à represents the  regular expression which we looking to search

match_parameter à this is optional. It helps to handle newline character, to retain multiline formatting, and control over case-sensitivity.  

·         ‘i’ à represents case insensitive matching.

·        ‘c’ à represents case sensitive matching [By Default ].    

·        ‘n’ à  Make the dot match any character, including newlines. By default, the dot matches any character except newlines.

·        ‘m’ à  Make the caret and dollar match at the start and end of each line. By default, these only match at the very start and the very end of the string.

Let us have a sample table (test_regexp)

NUM	NAME
11	MERGE EMP
22	MERGE DEPT
33	EMPDEPT

                    SELECT * FROM test_regexp

    WHERE REGEXP_LIKE (name, 'MERGE (EMP|DEPT)');                      

This regular expression searches for the pattern “EMP” or “DEPT” prefixed by “MERGE” .So the output will be a follows.

NUM	NAME
11	MERGE EMP
22	MERGE DEPT

REGEXP_SUBSTR:

            This function returns the actual substring that matches the regular expression pattern specified.

SYNTAX: REGEXP_SUBSTR (source_string, pattern , start_position , occurrence , match_parameter)

Start_Position à indicates the position to begin the search. By default 1, which means the search starts at the first character of source_string.

Occurrence à this is optional. Indicates the occurrence of pattern in source_string to search. By default is 1, which means the search starts for the first occurrence of pattern.

       SELECT REGEXP_SUBSTR (

          'Youare only here for a short visit. Dont hurry, dont worry. And be sure to smell the flowers along the way.'

        , '((hu)|(wo))rry')

          String

  FROM DUAL;

Output ->

String

hurry

This example searches for the pattern “hurry” or “worry” and displays the substring which occurs first.

Lets get the second occurrence of hurry/worry ?

       SELECT REGEXP_SUBSTR (

          'Youare only here for a short visit. Dont hurry, dont worry. And be sure to smell the flowers along the way.'

          , '((hu)|(wo))rry',1,2 )

          String

  FROM DUAL;

Output ->

String

Worry

REGEXP_REPLACE:

            This function searches for a pattern in the string and replaces each occurrence that matches the supplied regular expression with the specified string.

SYNTAX: REGEXP_REPLACE (source_string, pattern, replace_string, start_position, occurrence, match_option)                   

replace_string à The string to be replaced in source when matches the pattern. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then precede it with the escape character (\\)                               

Example:

     

       SELECT REGEXP_REPLACE ('Life is  beautiful   , Love your Life        '

                     , '( ){2,}'

                     , ' ')

  FROM DUAL;                                                                                                                                                                                                                                                  

This example hold more than 1 space in the supplied string. All the multiple spaces available will be replacing with single space in the string.                                  

 ( ){2,} – indicates 2 or more spaces.                                               

' '– second parameter holds a single space which replaces multiple spaces.

REGEXP_INSTR:

            This function searches a string for a given occurrence of a regular expression pattern. The occurrence to be found and the start position to search from can be specified. This function returns an integer indicating the position in the string where the match is found.

SYNTAX: REGEXP_INSTR (source_string, pattern, start_position, occurrence, return_option, match_option).

start_position à indicates the position to begin the search. By default 1.

return_option à  indicates what value to be returned in relation to the occurrence. If 0, then returns the position of the first character of the occurrence. If 1, returns the position of the character following the occurrence.

      Example:

      SELECT REGEXP_INSTR ('33 Alfred Street Sydney Australia 2000'

                     , '[[:digit:]]{4}$’') as pos_digit

  FROM DUAL;

This example returns the start position of the pin code of the supplied address.                                                             

[[:digit:]] -> indicated digit

[[:digit:]]{4} -> string with 4 or more digit

[[:digit:]] {4}$ -> 4 or more digit at the end of the string

REGEXP_COUNT:                                      

            This function counts the number of matches a regular expression has with the source text. If no match is found, returns the value 0. It complements the functionality of the REGEXP_INSTR function. This function can be utilized in Oracle 11g.

SYNTAX: REGEXP_COUNT (source_string, pattern, start_position, match_option);

      Example:

       SELECT REGEXP_COUNT ('A box contains 120 grapes and 2 oranges ','[0-9]', 1, 'i') count_string

          FROM dual;

        This example counts the number of single digit present in the string.