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

select case when REGEXP_LIKE('mohit_yadav@oracle.com', '^([[:alnum:]]+(_?|\.))[[:alnum:]]*@[[:alnum:]]+(\.([[:alnum:]]+)){1,2}$') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found
Let’s try to understand the pattern ^([[:alnum:]]+(_?|\.))[[:alnum:]]*@[[:alnum:]]+(\.([[:alnum:]]+)){1,2}$ in parts.
You are aware that the “^” marks the start of the string. Now let’s break ([[:alnum:]]+(_?|\.)) into two parts: [[:alnum:]]+ and (_?|\.)
[[:alnum:]]+ represents one or more occurrences of alphanumeric characters
(_?|\.) represents zero or one occurrence of underscore or dot. The “?” symbol here stands for zero or one occurrence; and “|” symbol represents OR. The expression (_?|\.) represents for a sub expression as a whole representing an optional underscore or dot. The parentheses help in grouping expressions together to represent one sub expression.
So now, ([[:alnum:]]+(_?|\.)) as a whole represents one or more occurrences of alphanumeric characters optionally followed by an underscore or dot.
Now that we have understood sub expressions we can move ahead with the rest of the pattern.
[[:alnum:]]* followed by zero or more occurrences of alphanumeric characters
@ followed by @
[[:alnum:]]+ followed by one or more occurrences of alphanumeric characters
(\.([[:alnum:]]+)){1,2} followed by the sub expression: dot followed by one or more occurrences of alphanumeric characters; from one to a max of two occurrences of the sub expression (Example: .com or .co.in)
Note: Did you notice the backslash before the dot? The backslash here is used as escape sequence for dot. Take extra care while testing patterns that include dot literals, as a Dot (.) alone stands for any character

• Validate Specific Number formats (Phone numbers, SSN, PAN, etc)

select case when regexp_like('02-3333-5555' ,'^[0-9]{2}-[0-9]{4}-[0-9]{4}$') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found
^ start of the string
[0-9]{2} two occurrences of digits from 0-9
- followed by hyphen
[0-9]{2} four occurrences of digits from 0-9
- followed by hyphen
[0-9]{4} four occurrences of digits from 0-9
$ end of the string
The above pattern can also be used to validate phone numbers with little customization.