Select columns based on condition in Snowflake

I am trying to find a solution to select columns in Snowflake based on a condition. For example I only want to select columns which contain the string “id” or only the numeric columns.

Is there any solution for these cases?

Best regards

Answer

You can use something as below. Following procedure query the account usage schema and generate a column list and then return a query based on table and column type. This can be extended

create or replace procedure column_list (TBL_NAME STRING, COL_TYPE STRING)
 returns string
language javascript

as
$$

  

  var sql_stmt = `select listagg(column_name,',') as col1 from snowflake.account_usage.columns 
where table_name = 'FOO' and table_schema = 'PUBLIC' and DATA_TYPE = '` + COL_TYPE + `' and DELETED IS NULL;`;
var create_log_table_stmt = snowflake.createStatement({ sqlText: sql_stmt });
var rs = create_log_table_stmt.execute();

  rs.next();
  var result = rs.getColumnValue(1);
  
  var final_query = "select " + result + " from " + TBL_NAME;
  return final_query;
$$
;

call column_list ('FOO', 'NUMBER');