Using Regular Expression function (Regex) in SQL - Part IV

• Consecutive Occurrences

Let’s try to search for two consecutive occurrences of letters from a-z in the following example.

select regexp_substr('Australiaaaaaa','([a-z])\1', 1,1,'i') from dual;

Output: aa
([a-z]) character set a-z
\1 consecutive occurrence of any character in the class [a-z]
1 starting from 1st character in the string
1 First occurrence
i stands for case insensitive

Now let’s try to search for three consecutive occurrences digits from 6 to 9 in the following example.

select case when regexp_like('Patch 10999 applied' ,'([6-9])\1\1') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found
Note: Here we didn’t specify the position or occurrence in the above sql query. By default, oracle will search for the first occurrence of the matching pattern from the start of the string and will stop when it encounters appropriate match. If no match is found it stops at the end of the string

• Formatting Strings

select regexp_replace('04099661234', '([[:digit:]]{2})([[:digit:]]{4})([[:digit:]]{5})', '(\1) \2-\3') as Formatted_Phone from dual;

Output: (040) 9966-1234
We tried to format a phone number in the above example. Let’s understand the match pattern and replacing string.
Our match pattern is ^([[:digit:]]{3})([[:digit:]]{4})([[:digit:]]{4})$
([[:digit:]]{2}) 2 digits
([[:digit:]]{4}) followed by 4 digits
([[:digit:]]{5}) followed by 5 digits
Why did I group the digits into sub expressions using the parentheses? I could have simply searched for [[:digit:]]{11} as my input string comprises of 11 digits only. To understand the reason, let’s look at the replacing string (\1) \2-\3
( includes a opening parenthesis
\1 represents the first sub group expression in our match pattern i.e. 04
) includes a closing parenthesis
\2 represents the second sub group i.e. 0996
- includes a hyphen
\3 represents the third sub group i.e. 61234

We’ll see some more formatting in our next example.

select regexp_replace('04099661234', '^([[:digit:]]{1})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{4})$', '+91-\2-\3-\4') as Formatted_Phone from dual;

Output: +91-409-966-1234

In the next example, we shall include a space between every character.

select regexp_replace('ORACLE', '(.)', '\1 ') as Output from dual;

Output: O R A C L E
Some more patterns
In the below example let’s look for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.)

select regexp_substr('Go to http://www.oracle.com/products and click on database','http://([[:alnum:]]+\.?){3,4}/?') result from dual;

Output: http://www.oracle.com/select regexp_substr('Go to http://www.oracle.co.uk/products and click on database','http://([[:alnum:]]+\.?){3,4}/?') result from dual;

Output: http://www.oracle.co.uk/
http:// The characters http://
([[:alnum:]]+\.?) represents the sub expression: one or more occurrences of alphanumeric characters followed by a dot optionally
{3,4} minimum 3 occurrences of the above sub expression to a max of 4
/? followed by forward slash optionally

Let’s now try to extract the third value from a csv string.

select regexp_substr('1234,New Delhi,India,1.5.105','[^,]+',1,3)as Output from dual;

Output: India
[^,]+ one or more occurrences of non comma characters
1 specifies the starting position
3 Third match