Replacing of complex LIKE with REGEXP_LIKE from Oracle 10g

Oracle Database 10g offers four regular expression functions. You can use these equally in your SQL and PL/SQL statements. Among these REGEXP_LIKE is the most useful. Like LIKE operator it returns boolean values. Thus this can be easily used in WHERE and HAVING clause. The header of this function is -

REGEXP_LIKE(source, regexp, modes)

To know more about this function visit http://www.oracle-developer.net/.

Also here is a recommended site for regular expression http://www.regular-expressions.info

The third parameter is optional and it is rarely used.

Here I will show you how it makes the query simple and faster -

'^(880|88018)?7777[0-9]{0,2}$' - this regular expression matches every starts with 880 or 88018 or none of these, then 7777 then ends or ends after one or two digits. So it matches the following strings -

8807777, 880187777, 7777, 77776, 777758.

So, if you want to query a database table with this filter on a column, you can write the following query -

select * from table where regexp_like(colA, '^(880|88018)?7777[0-9]{0,2}$')

or

select * from table where colA = '7777' or colA='8807777' or colA='880187777'
or colA like '7777_' or colA like '8807777_' or colA like '880187777_'
or colA like '7777__' or colA like '8807777__' or colA like '880187777__'

Thus regexp_like() function makes the query simple, comprehensive and easy to manage. In some case you may find it is absolutely hard to write query using like where that can be easily written using regexp_like().