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

select case when regexp_like('United States OF America' ,'of', 'c') then 'Match Found' else 'No Match Found' end as output from dual;

Output: No Match Found
“c” stands for case sensitive
In the above example we tried to search for the pattern “of” exactly matching the case and hence we didn’t find a match.
Let’s now try a search for “of” in the source string irrespective of the case in the below example:

select case when regexp_like('United States OF America' ,'of', 'i') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found
“i” stands for case insensitive

• Match nth character

select case when regexp_like('mo*it' ,'^..[^[:alnum:]]') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found
In the above example we tried to search for a special character at the 3rd position of the input string “mo*it”

Let’s now try to understand the pattern '^..[^[:alnum:]]'
^ marks the start of the string
. a dot signifies any character (.. 2 dots specify any two characters)
[^[:alnum:]] Non alpha-numeric characters (^ inside brackets specifies negation)
Note: The $ is missing in the pattern. It’s because we are not concerned beyond the 3rd character and hence we need not mark the end of the string. (..[^[:alnum:]]$ would mean any two characters followed by a special character and no characters beyond the special character)