REGEX SQL

As you know regex is a powerful string manipulation function used to filter the strings,numbers,characters etc very effectively. Below are the useful functions to pull desired filter

REGEXP_SUBSTR

REGEXP_SUBSTR(source_string,pattern [,position[,occurence[,return_option[,match_parameter]]]]

kish@exdbx<>select regexp_substr('MG-KISHAN-SKJFLS-JDS-ADLS','-[^-]+-') as WHOAMI from dual;

WHOAMI
--------
-KISHAN-

[[:digit:]] is a parameter used in regex_substr to filter only digits from string

kish@exdbx<>select regexp_substr('i was born on 1990','[[:digit:]]+') from dual;

REGE
----
1990

REGEXP_LIKE is a function which can be used in where clause of the select query. Below is an example.

kish@exdbx<>@colfrmt



Procedure created.

Elapsed: 00:00:00.88

Procedure created.

Elapsed: 00:00:00.10

kish@exdbx<>select country,order_priority,order_date from sales_tab where regexp_like(ORDER_DATE,'MAR-26+');

......
"Marshall Islan C               01-MAR-26
ds

"Niger          H               01-MAR-26
"Montenegro     H               01-MAR-26
"Norway         M               01-MAR-26
"Burundi        L               01-MAR-26
"Belarus        H               01-MAR-26
"Sri Lanka      H               01-MAR-26
"The Gambia     H               01-MAR-26
"East Timor     H               01-MAR-26
"Vanuatu        C               01-MAR-26
"Senegal        H               01-MAR-26
"Belgium        C               01-MAR-26

880 rows selected.

[[:punct:]] is a parameter used in regexp_substr to filter all the characters like punctuation marks in the select query

kish@exdbx<>select regexp_substr('Be persistent and perseverant : Be consistent :369','[[:punct:]]') from dual;

R
-
:

[[:punct:]] parameter usage in regexp_like function

kish@exdbx<>select country from sales_tab where regexp_like(country,'[[:punct:]]')
;

COUNTRY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"Grenada
"Turkey
"Laos
"Guinea
"Tonga
"Cote d'Ivoire
"Uganda
"Mali
"Nicaragua
..............

REGEXP_INSTR

Syntax:

REGEXP_INSTR(source_string,pattern [,position[,occurence[,return_option[,match_parameter]]]]

To find the position of a number and string

kish@exdbx<>select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]') as POSITION from dual;

                                POSITION
----------------------------------------
                                      31

Elapsed: 00:00:00.01

To find the next position of the number

1939 is the number and 9 is the second position of the first number

select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]',1,2,1) as POSITION from dual;

                                POSITION
----------------------------------------
                                      33

To find the next position of the next number

1945 is the number and 1 is the next position of the number

kish@exdbx<>select regexp_instr('Second world war started from 1939 till 1945','[[:digit:]]',1,5,1) as POSITION from dual;

                                POSITION
----------------------------------------
                                      42

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s