How to query columns following the same pattern in a Snowfake table or view?

Question

I'd like to select some columns which have names following the same pattern. For instance:

SELECT PRICE% FROM DB.SCHEME.VIEW

From that I'm trying to select all fields from a view or table which names start with the string "PRICE", like "PRICE_US", "PRICE_CA", "PRICE_EU", for instance.

What would be the correct way of doing that?

Answer 1

It is possible to achieve such effect with Snowflake Scripting and dynamic SQL:

Prep:

CREATE OR REPLACE TABLE tab
AS
SELECT 1 AS PRICE_EU, 2 AS PRICE_US, 3 AS PRICE_CA, 4 AS col, 5 AS col2;

SELECT * FROM tab;

---

The idea is to generate column list using INFROMATION_SCHEMA.COLUMNS:

DECLARE 
  res RESULTSET;
  sql STRING;
  col_list STRING;
BEGIN 

    SELECT LISTAGG(COLUMN_NAME, ' ,') WITHIN GROUP(ORDER BY COLUMN_NAME)
    INTO col_list
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ILIKE 'TAB'
      AND COLUMN_NAME ILIKE 'PRICE%';
      
    sql := 'SELECT ' || col_list || ' FROM tab';
    
    res := (EXECUTE IMMEDIATE :sql);
  
    RETURN TABLE(res);
END;

Output: