REGEXP_LIKE In Oracle With Example

REGEXP_LIKE: So first question: Why do we use it in Oracle. Simple answer ( not the most comprehensive one) : To do a pattern search. Or we can say that it is an extension of function LIKE. It can do everything that function LIKE can do, plus it extends the usage for patterns against the specific characters function “LIKE” can process. Confused? Let me simplify, what if you are asked to search all the values in a column having “#” in it. You will simply write the where clause as
WHERE column1 like ‘%#%’

But what if you are asked to search all the values having special characters ( such as !,@,#,$ etc) in it. Now you can’t really write a code line for each special character you need to filter. Here comes our hero REGEXP_LIKE in the game.
But before we jump to the syntax, here are couple of articles which revolve around REGEXP and can be helpful
REGEXP_REPLACE In Oracle With Real Life Example
REGEXP_INSTR in Oracle With Example

Syntax:

REGEXP_LIKE ( expression, pattern [, match_parameter ] ).

in the function, expression is the value in which you want to look for the pattern. and most probably it will be the column name of your table. Pattern is what you are looking for. Few examples are:

[a-z] means any lower case alphabet from “a” to “z”.
[A-Z] denotes any upper case alphabet from “A” to “Z”
[0-9] means any number.

If you want to exclude any pattern from the search, you have to use the “^”. e.g if you want to exclude any lower case character, you will write the code like [^a-z]

Similarly if you want to skip upper case as well as lower case character both, you’ll have to write code something like [^a-zA-Z]

( For details of pattern and match_parameter, I found this article useful:
https://www.techonthenet.com/oracle/regexp_like.php)

In my experience, practically this function is used only when you have to export / import the data having special characters which you want to either flag to the user or want to skip them altogether.

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...

REGEXP_LIKE Example:

So I had a requirement where we were exporting the AR Invoice data to a third party system. Data consisted along with other column, Invoice Line Number and Description. Field Description had some values using special characters like “:” etc. Due to some limitations of the destination system, we had to filter out the lines having these special characters.

So let me show you the simplified version of the query I used and the corresponding output.

Query1

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

As you can see, except line 1, every other line has a special character ( Ignoring the white space). So our requirement was to write the query such a way that only first line is picked and here is what I did:

REGEXP_LIKE Query1

TRX_NUMBER	LINE_NUMBER	DESCRIPTION
10420909	1	2021 LC CONV V8 10AT

Trick behind the magic was that REGEXP_LIKE (rctla.description,'[^a-zA-Z0-9 ]’) gave me all the rows which have any character other than a-z, A-Z, 0-9, white space. a NOT before the REGEXP_LIKE ensured that all the rows selected by REGEXP_LIKE are excluded. Let’s see what happens if I remove the NOT from the clause. Query becomes this:

REGEXP_LIKE Query2

And the output:

TRX_NUMBER	LINE_NUMBER	DESCRIPTION
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV
10420909	3	LEXUS F/AID KIT W/O VELCRO
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)
10420909	5	CFM ORCHE (INT: 40)

Related Posts:

REGEXP_REPLACE In Oracle With Real Life Example
REGEXP_INSTR in Oracle With Example

Reference
https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm

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.

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