REGEXP_INSTR in Oracle With Example

REGEXP_INSTR is a function given by Oracle to find a pattern in a string and return the position of it. But in my experience, rather than finding the presence of a pattern, this function has higher utility in finding the characters not present in a given set of characters e.g. rather than saying give me the position of first alphanumeric, you are most probably going to use this function to find out first non numeric character’s position in the string.

REGEXP_INSTR Syntax

Syntax of the function is:
REGEXP_INSTR( string, pattern [, start_position [, nth_appearance [, return_option [, match_parameter [, sub_expression ] ] ] ] ] )

To get more details of the function, you can refer to following links:
https://www.techonthenet.com/oracle/functions/regexp_instr.php

REGEXP_INSTR Example

So we had an Receivable invoice which had special characters in the description. And below is the query we ran and below are the results. If you look at the REGEXP_INSTR function, you will see that we have used “^” to ignore few patterns i.e. alphabets, both in upper and lower case, Numeric characters, white space and Double Quote. Double quote was included just to show that a special character can be ignored if need arises.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Query1

TRX_NUMBER	LINE_NUMBER	DESCRIPTION	SPL_CHAR_POS
10420909	1	2021 LC CONV V8 10AT	0
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV	2
10420909	3	LEXUS F/AID KIT W/O VELCRO	8
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)	19
10420909	5	CFM ORCHE (INT: 40)	11

REGEXP_INSTR Function also gives us some flexibility. Like Start_Position and Nth Occurrence. So let’s tweak the query a bit and see what happens. Please pay attention to additional parameters added in the REGEXP_INSTR function. First additional param has the value 3 which denotes that search should begin from the third character and second additional parameter is having the value of 2 which means one needs second occurrence.

Query2

TRX_NUMBER	LINE_NUMBER	DESCRIPTION	SPL_CHAR_POS
10420909	1	2021 LC CONV V8 10AT	0
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV	20
10420909	3	LEXUS F/AID KIT W/O VELCRO	18
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)	37
10420909	5	CFM ORCHE (INT: 40)	15

Now compare the output of both the queries and special attention to line_number 2. First query gave the special character ( other than the one which we have asked it to ignore) position was 2, which was for character “:”. But in second query, we have asked to start from position 3 ( third param in the REGEXP_INSTR) and find the second occurrence ( Fourth Param in REGEXP_INSTR). Here, the query gave us position 20 which is the position of “)”. So now that you know the basics, go ahead and play around. But keep an eye on the performance too. In my experience, I try to avoid it as much as I can but then, sometimes you have to bite the bullet.

Related Posts

REGEXP_LIKE In Oracle With Example
REGEXP_REPLACE In Oracle With Real Life Example

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..