fbpx

Oracle REGEXP_INSTR Examples

Regular expressions are patterns used to match character combinations in strings. Since version 10g, Oracle has introduced support for regular expression with different functions, this post focuses on the Oracle REGEXP_INSTR function and explains how to use it.

Description

The Oracle REGEXP_INSTR returns the location of a regular expression in a string.

Syntax

REGEXP_INSTR
(string, pattern [, position [, occurrence [, return_position [, match_parameter]]]])
  • string – the character expression to be searched.
  • pattern – the regular expression matching pattern.
  • position
    • A numeric position at which the search begins.
    • If position is not specified, the search starts at the beginning of string.
  • occurence –
    • lets you specify which occurrence of a the match to return.
    • If omitted, the default value is 1, the first occurrence.
  • return option –
    • If a value of 0 is provided, the function returns the position of the first character in the match.
    • If a value of  1 is provided, the function returns the position of the first character after the match.
    • If omitted, the default value is 0.
  • match parameter – lets you to change the default matching behaviour of the Oracle REGEXP_INSTR function, for example, change the search from case sensitive to case insensitive (Optional) .

REGEXP_INSTR Examples

We’ll start by creating a table called Names, based on its values, the following Oracle REGEXP_INSTR examples will perform different regular expression searches.

CREATE TABLE names
AS
SELECT rownum AS ID, last_name AS NAME
FROM hr.employees ;

The following Oracle REGEXP_INSTR example would retrieve the position of the first ‘t‘ character for each name:

SELECT name , regexp_instr(name ,'t')
FROM names;

NAME                      REGEXP_INSTR(NAME,'T')
------------------------- ----------------------
Abel                                           0
Ande                                           0
Atkinson                                       2
Austin                                         4
Baer                                           0

The next Oracle REGEXP_INSTR example would retrieve the position of the first ‘e‘ character for each name:

SELECT name , regexp_instr(name ,'e')
FROM names; 

NAME                      REGEXP_INSTR(NAME,'E')
------------------------- ----------------------
Abel                                           3
Ande                                           4
Atkinson                                       0
Austin                                         0
Baer                                           3

The next Oracle REGEXP_INSTR example would retrieve the position of the first ‘ae‘ letter sequence for each name:

SELECT name , regexp_instr(name ,'ae')
FROM names;

NAME                      REGEXP_INSTR(NAME,'AE')
------------------------- -----------------------
Abel                                            0
Ande                                            0
Atkinson                                        0
Austin                                          0
Baer                                            2

Using Square Brackets

In the next example we would use the square brackets to display the position of the first ‘a‘ or ‘e‘ characters for each name:

 
SELECT name , regexp_instr(name ,'[ae]')
FROM names;

NAME                      REGEXP_INSTR(NAME,'[AE]')
------------------------- -------------------------
Abel                                              3
Ande                                              4
Atkinson                                          0
Austin                                            0
Baer                                              2

Changing the default position

In this example –

  • The values of the first column would represent the first occurrence of the [ae] pattern, starting at position 1 of the text.
  • The values of the second column would represent the first occurrence of the [ae] pattern, starting at position 4 of the text.

  SELECT  id,
          name ,
          regexp_instr(name ,'[ae]')   AS COL_1,
          regexp_instr(name ,'[ae]',4) AS COL_2
  FROM names
  WHERE id BETWEEN 10 AND 20 ;

        ID NAME                       COL_1  COL_2
---------- ------------------------- ------ ------
        10 Bernstein                      2      7
        11 Bissot                         0      0
        12 Bloom                          0      0
        13 Bull                           0      0
        14 Cabrio                         2      0
        15 Cambrault                      2      6
        16 Cambrault                      2      6
        17 Chen                           3      0
        18 Chung                          0      0
        19 Colmenares                     5      5
        20 Davies                         2      5

Changing the default occurence

In this example –

  • The values of the first column would represent the first occurrence of the [ae] pattern, starting at position 1 of the text.
  • The values of the second column would represent the first occurrence of the [ae] pattern, starting at position 4 of the text.
  • The values of the third column would represent the second occurrence of the [ae] pattern, starting at position 4 of the text.
SELECT    id,
          name ,
          regexp_instr(name ,'[ae]')     AS COL_1,
          regexp_instr(name ,'[ae]',4)   AS COL_2,
          regexp_instr(name ,'[ae]',4,2) AS COL_3
FROM names
WHERE id BETWEEN 10 AND 20 ;

        ID NAME                       COL_1  COL_2  COL_3
---------- ------------------------- ------ ------ ------
        10 Bernstein                      2      7      0
        11 Bissot                         0      0      0
        12 Bloom                          0      0      0
        13 Bull                           0      0      0
        14 Cabrio                         2      0      0
        15 Cambrault                      2      6      0
        16 Cambrault                      2      6      0
        17 Chen                           3      0      0
        18 Chung                          0      0      0
        19 Colmenares                     5      5      7
        20 Davies                         2      5      0

Changing the default return option

In this query  –

  • The values of the first column would represent the first occurrence of the [ae] pattern, starting at position 1 of the text.
  • The values of the second column would represent the first occurrence of the [ae] pattern, starting at position 4 of the text.
  • The values of the third column would represent the second occurrence of the [ae] pattern, starting at position 4 of the text
  • The values of the fourth column would represent the second occurrence of the [ae] pattern, starting at position 4 of the text. Here we explicitly specified a return option with a value of 0, which is the default, so no difference between the values of this column and the previous one.
  • The values of the fifth column would represent the second occurrence of the [ae] pattern + 1, starting at position 4 of the text. Here we explicitly specified a return option with a value of 1, in this case the function returns the position of the first character after the match.
  • The values of the sixth column would represent the second occurrence of the [ae] pattern, starting at position 4 of the text. Here we explicitly specified the match parameter and made a case insensitive search .
  SELECT       id,
               name ,
               regexp_instr(name ,'[ae]')              AS COL_1,
               regexp_instr(name ,'[ae]',4)            AS COL_2,
               regexp_instr(name ,'[ae]',4,2)          AS COL_3,
               regexp_instr(name ,'[ae]',4,2,0)        AS COL_4,
               regexp_instr(name ,'[ae]',4,2,1)        AS COL_5,
               regexp_instr(name ,'[ae]',4,2,0, 'i')   AS COL_6
  FROM names
  WHERE id BETWEEN 10 AND 20 ;

        ID NAME         COL_1  COL_2  COL_3  COL_4  COL_5  COL_6
---------- ----------- ------ ------ ------ ------ ------ ------
        10 Bernstein        2      7      0      0      0      0
        11 Bissot           0      0      0      0      0      0
        12 Bloom            0      0      0      0      0      0
        13 Bull             0      0      0      0      0      0
        14 Cabrio           2      0      0      0      0      0
        15 Cambrault        2      6      0      0      0      0
        16 Cambrault        2      6      0      0      0      0
        17 Chen             3      0      0      0      0      0
        18 Chung            0      0      0      0      0      0
        19 Colmenares       5      5      7      7      8      7
        20 Davies           2      5      0      0      0      0

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…