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;

Output: Match Found
In the above example we tried to check if the source string contains only 6 alphabets and hence we got a match for “Oracle”.
Now let’s try to understand the pattern '^[[:alpha:]]{6}$'
^ marks the start of the string
[[:alpha:]] specifies alphabet class
{6} specifies the number of alphabets
$ marks the end of the string