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
