Rick Caminiti dotCOM

In a like statement, “%” is a wildcard for everything after or before it and “_” is a wildcard for one character.  Observe:

LIKE ‘%Z’ would return ANYthing that ended with Z.

LIKE ‘%Z%’ would return ANYthing with a Z in it.

LIKE ‘Z__’ would return anything that started with Z and had at least 2 characters after it, like:

ZOO, ZXY, ZAP

but would not return:

ZA, ZO, Z, or XYZ

LIKE ‘Z%’ would return anything that STARTED with Z.

LIKE ‘_Z%’ would return anything that had one character, then a Z, then anything or nothing else like:

YZX, YZ, ZZZ

but would not return:

XYZ, ABCZ

However, if you want to look for the literal value of ‘%’ or ‘_’, you need to escape it.  For instance, if you want to look for anything that begins with ‘Z_’, then you would need to escape the ‘_’, otherwise, you would be looking for anything that started with ‘Z’ followed by one wildcard character ‘_’, followed by a wildcard of any number of characters ‘%’, returning anything that just started with ‘Z’ and had at lease one character after it.

select table_catalog, column_name, table_schema, table_name
from information_schema.columns
where column_name like 'Z\_%' escape '\'

This would only return data that started with 'Z_'.