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.
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.
String is any string in which user wants to find out the specific position of the pattern.
Pattern is any string pattern, which is used as regular expression.
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.
Match parameters are nothing but the parameters used to modify the matching behavior of given string.
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.
These are above some important parameters of REGEXP_INSTR function. Let us check some important examples of REGEXP_INSTR function.
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.
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.
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.
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.
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.
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.
Here we are using match parameter as ‘i’ which will reduce the case sensitiveness and fetches the position of first vowel which is ‘O’.
User can find out the position of any special character using REGEXP_INSTR function.For finding out dot user needs to use ./ option.
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.