Using REGEXP_LIKE to perform multiple wildcard matchings

The Oracle in clause in queries in invaluable in shortening SQL queries. Without it, you would need to perform long and arduous AND clauses that greatly muck up the readability of SQL statements.

Long SQL statements where one column could have multiple values such as this:

SELECT * FROM TABLE WHERE COL1 = 'X' OR COL1 = 'Y' OR COL1 = 'Z' OR COL1 = 'A';

Can be reduced to the following:

SELECT * FROM TABLE where COL i ('X','Y','Z','A');

Unfortunately, the IN clause only allows for checking of exact matches. If will not do a wildcard match (e.g. Z%)

So this type query:

SELECT * FROM TABLE WHERE COL1 like 'X%' OR COL1 like 'Y%' OR COL1 like 'Z%' OR COL1 like 'A%';

Will not work with a standard IN clause.

The solution for this is to use Oracle's REGEXP_LIKE to perform a REGEX match.

SELECT * FROM TABLE WHERE REGEXP(COL1,'^X|^Y|^Z|^A')

Remember under regex, the ^ marks the beginning of a line/column and $ marks the end of a line/column

the pipe | denotes an OR Match.