Dynamic LIKE in Where Clause statement

0
votes

I've been trying my best to achieve dynamic query for the LIKE statement

From the below SQL query instead of doing ilike for every value and it may grow in large. I cannot rewrite the query again and again for new table_name value .. I can store those values in a separate table, But How do I achieve it dynamically

  SELECT
        t.table_name as table_name,
        t.table_schema as table_schema
    FROM
       information_schema.tables T
    WHERE  (table_schema  ilike 'stage' and table_name  like 'ABC%') or (table_schema  ilike 'stage' and table_name  like 'EFG%');

I can have another table with a list of values like below

create or replace table tempdw.blk_table; ( db_name varchar, tbl_expr varchar );

insert into tempdw.blk_table values ('stage','ABC%'); insert into tempdw.blk_table values ('stage','EFG%');

select * from tempdw.blk_table;

enter image description here

2
sqlsnowflake-cloud-data-platformsnowflake-schema

2 Answers

1
votes

If you are just looking for a set of tables with prefixes, you could use regexp_like():

where table_schema ilike 'stage' and
      regexp(lower(table_name), '^abc|efg')
1
votes

If you have a table with tables and schemas in them, you could always collate your join to take care of the case-sensitive part:

SELECT
    t.table_name as table_name,
    t.table_schema as table_schema
FROM
   information_schema.tables T
JOIN
   table_names tn
ON collate(t.table_name , 'en-ci') like collate(tn.table_name , 'en-ci')
AND collate(t.table_schema, 'en-ci') = collate(tn.schema_name, 'en-ci');