REGEXP_INSTR in Oracle :

In my previous article, I have given the idea about different Regular Expressions. These kind of regular expressions are used for validation in SQL and PL SQL. In this article, I will try to explain about another regular expression function, which is REGEXP_INSTR, which is used to find the position of the pattern of specific string. Regular expressions are patterns used to match character combinations in strings.This post focuses on the  REGEXP_INSTR in Oracle function and explains how to use it.

Syntax :

REGEXP_INSTR( string, pattern [, start_position [, nth_appearance [, return_option [, match_parameter [, sub_expression ] ] ] ] ] );

The regexp_instr function is a very powerful to extract patterns from inside a string. The following match patterns are very useful to check the position of the string.

1.String:

String is any string in which user wants to find out the specific position of the pattern.

2.Pattern:

Pattern is any string pattern, which is used as regular expression.
3. Position:

Position is any number, which is used as the beginning of the string .If the position value is zero then the function will start from the string position 0.If user gives any position other than zero then the starting point of function is that number and the execution starts with that specific character position.

4.Match parameters:

Match parameters are nothing but the parameters used to modify the matching behavior of given string.

Match parameters:

 i – i  parameter is most important parameter which is used to introduce case-insensitivity of the given         string.
c – c parameter is used to keep the case of string as it is.
n – to make the dot (.) match new lines as well
m – to make ^ and $ match beginning and end of a line in a multi-line string

These are above some important parameters of REGEXP_INSTR function. Let us check some important examples of REGEXP_INSTR function.

 REGEXP_INSTR Oracle SQL Examples :

1.Finding position of the character in the string :

The REGEXP_INSTR in Oracle function is used to find the position of the character in the string.I will use the match parameter as c to check the case sensitiveness of given string or given character.

Query:

SELECT REGEXP_INSTR (‘Oracle is best database programming language language ‘, ‘o’, 1, 1, 0, ‘c’) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

27

 

2.Finding position of the string:

The REGEXP_INSTR in Oracle function is used to find the position of specific string occurances.It is used to find the position of character or series of parameter.

Query :

SELECT REGEXP_INSTR (‘Oracle is best database programming language language ‘, ‘language’, 1, 1, 0, ‘i’) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

37

The above statement will calculate the position of the string ‘language’ .Let us change the option and position from 0 to 1 and check the position of the string named language. The REGEXP_INSTR in Oracle function is used to calculate the position of language after the first occurrence.

Query:

SELECT REGEXP_INSTR (‘Oracle is best database programming language and language ‘, ‘language’, 1, 1, 1, ‘i’) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

45

3.Find out the digits from the string :

The ‘\d’ is useful option, which is used to find out the digit position from the given string. Always remember that this will fetch the position of the first digit in the given string.

Query:

SELECT REGEXP_INSTR (‘Oracle is best database programa11 ming language 1 language ‘,’\d’,’1′) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

33

4.Find out the position of specific  with using OR(|) operator:

User can find out the position of vowels in given string.User needs to use the pipe operator to find out the position of the specific vowel.

Query :

SELECT REGEXP_INSTR (‘Oracle is best database programa11 ming language 1 language ‘,’a|e|i|o|u’) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

3

The above query will find the position of a or e or i or o or u.We are not using match parameter i so the above query will fetch the result as 3.

5.Find out position of vowel :

User can find out the position of the vowel using following query

Query:

SELECT REGEXP_INSTR (‘Oracle is best database programa11 ming language 1 language ‘,'[aeiou]’, 1, 1, 0, ‘i’) regexp_instr FROM dual;

Output:

REGEXP_INSTR

———————-

1

Here we are using match parameter as ‘i’ which will reduce the case sensitiveness and fetches the position of first vowel which is ‘O’.

6.Find out the position of Dot from the query :

User can find out the position of any special character using REGEXP_INSTR function.For finding out dot user needs to use ./ option.

Query :

SELECT REGEXP_INSTR (‘Oracle.is.best database programming language.’, ‘\.’, 1, 2, 0, ‘i’) regexp_instr FROM dual;

Output :

REGEXP_INSTR

———————-

10

These are some examples of REGEXP_INSTR which is used to find out the position of the character. Hope everyone like this article.Dont forget to comment on comment section.