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?
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:
