Bulk column DROP in snowflake --- syntax for WHERE first character of column LIKE '

A Snowflake table has been populated with 2000 columns due to each property being sent as a KEY instead of VALUE.

Need to drop these columns.

All of them begin with _ e.g. '_ASD3234E2

To test my syntax will be correct for the final ALTER TABLE command, I'm first trying a SHOW COLUMNS command.

SHOW COLUMNS LIKE '_%' IN TABLE DELIVERY_VIEW_DETAIL;

This is showing all columns in the table still though.

CodePudding user response：

As @Austin mentioned as well, it needs to be escaped -

SHOW COLUMNS LIKE '\\_%' IN TABLE TRANS_1 ;
 ------------ ------------- ------------- 
| table_name | schema_name | column_name |
|------------ ------------- ------------- 
| TRANS_1    | PUBLIC      | _COL1       |
| TRANS_1    | PUBLIC      | _COL2       |
| TRANS_1    | PUBLIC      | _COL3       |
| TRANS_1    | PUBLIC      | _COL4       |
 ------------ ------------- -------------

Without escape -

SHOW COLUMNS LIKE '_%' IN TABLE TRANS_1 ;
 ------------ ------------- ------------- 
| table_name | schema_name | column_name |
|------------ ------------- ------------- 
| TRANS_1    | PUBLIC      | ID          |
| TRANS_1    | PUBLIC      | _COL1       |
| TRANS_1    | PUBLIC      | _COL2       |
| TRANS_1    | PUBLIC      | _COL3       |
| TRANS_1    | PUBLIC      | _COL4       |
 ------------ ------------- -------------

Single escape -

SHOW COLUMNS LIKE '\_%' IN TABLE TRANS_1 ;
 ------------ ------------- ------------- 
| table_name | schema_name | column_name |
|------------ ------------- ------------- 
| TRANS_1    | PUBLIC      | ID          |
| TRANS_1    | PUBLIC      | _COL1       |
| TRANS_1    | PUBLIC      | _COL2       |
| TRANS_1    | PUBLIC      | _COL3       |
| TRANS_1    | PUBLIC      | _COL4       |
 ------------ ------------- -------------

All columns -

SHOW COLUMNS IN TABLE TRANS_1 ;
 ------------ ------------- ------------- 
| table_name | schema_name | column_name |
|------------ ------------- ------------- 
| TRANS_1    | PUBLIC      | ID          |
| TRANS_1    | PUBLIC      | _COL1       |
| TRANS_1    | PUBLIC      | _COL2       |
| TRANS_1    | PUBLIC      | _COL3       |
| TRANS_1    | PUBLIC      | _COL4       |
 ------------ ------------- -------------