Thursday, July 31, 2014

Oracle Regular Expressions : REGEXP_LIKE for combination of IN | OR | LIKE

REGEXP_LIKE is an interesting solution for IN operator or OR  or LIKE

When we want to find the cities which stating letter 'Lon' or 'C' or etc .. and we do not know we can not user  IN or LIKE or  OR to fulfill this requirement as follows

SELECT * FROM Customers
WHERE City IN ('Paris','Colombo',.....); 

Above is not ideal solution but following is ideal solution with may parameters

SELECT * FROM Customers
WHERE City LIKE 'Lon%' or  City LIKE 'Col%' or .......;

Here is an ideal solution with single param solution with REGEXP_LIKE

SELECT * FROM Customers
WHERE REGEXP_LIKE (City,'^(Lon|Col|...|...)');