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.

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

• Search and replace white spaces

select regexp_replace('Each     One   Teach      One','[[:blank:]]{2,8}',' ') from dual;

Output: Each One Teach One
In the above example we have replaced multiple white spaces (2 to 8) in the source string with single space.
Let’s now try to understand the pattern [[:blank:]]{2,8}
Now that you are familiar with the patterns you may be wondering about the missing (^$) anchors in the pattern. These have been skipped purposefully. In the source string we wanted to search for multiple spaces anywhere in the string. So we need not specify the start (^) and end ($) anchors.
Note: ^[[:blank:]]{2,8}$ would mean a pattern consisting of min of 2 spaces to a max of 8 and no other
characters and we won’t find a match for the above input. 

• Validate email

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

• Validate a string for lower case alphabets only

select case when regexp_like('Mohit' ,'^([[:lower:]]{3,12})$') then 'Match Found' else 'No Match Found' end as output from dual;
Output: No Match Found
If we change the input to “mohit”, we get a match. In the above example we tried to check if the source string contains only lower case alphabets with a string length varying from minimum of 3 to max of 12.
The pattern is similar as above but {3,12}. {3,12} specifies at least 3 alphabets but not more than 12

• Case Sensitive Search

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

Everyday most of us deal with multiple string functions in Sql. May it be for truncating a string, searching for a substring or locating the presence of special characters.

The regexp functions available in Oracle can help us achieve the above tasks in a simpler and faster way. I have tried to illustrate the behavior of the regexp functions with few illustrations below. The patterns can be used with any of the regular expression functions.

• Validate a string for alphabets only

select case when regexp_like('Oracle' ,'^[[:alpha:]]{6}$') then 'Match Found' else 'No Match Found' end as output from dual;