I am running this query in snowflake:
select *
from my_database.information_schema.tables
where
table_schema NOT LIKE '%information%';
When I look at the records, some of them have INFORMATION_SCHEMA as the table_schema.
Why is my filter not working?
LIKE is case sensitive, where-as ILIKE is case insensitive. And your two strings are different cases. So I suggest you swap to ILIKE
SELECT 'a' LIKE 'A' as "a_like_A", 'a' ILIKE 'A' as "a_ilike_A";
gives:
a_like_A a_ilike_A
FALSE TRUE
When I look at the records, some of them have INFORMATION_SCHEMA as the table_schema.
When an identifier is unquoted, it is stored and resolved in uppercase.
The issue is you are comparing uppercase vs lowercase string which are different.
where table_schema LIKE '%information%'; -- this comparison will not work
Other way to compare:
where table_schema LIKE UPPER('%information%');
It is worth noting that SHOW TABLES LIKE '<patern>' is case-insensitive by design, and will return a match for the following regardless of version used:
SHOW TABLES LIKE '%information%';
SHOW TABLES LIKE '%INFORMATION%';
SHOW TABLES LIKE '%Information%';