Snowflake Supports SELECT * ILIKE/REPLACE
Snowflake keeps the expansion of the SELECT * syntax by introducing two new features: “SELECT * ILIKE” and “SELECT * REPLACE”. This blog goes through both new syntaxes, discusses their usage scenarios, and how to use them in combination with other SELECT * keywords.
SELECT * ILIKE
In certain circumstances, you may need to select all columns in a table whose names align with a specific pattern. Consider a scenario where you’re trying to retrieve all columns with names containing the term “device”. Or imagine a situation where you have a series of features for two different iterations, each separated by a suffix, such as a_1, b_1, c_1, a_2, b_2, c_2. In this case, you might want to select all data corresponding to the first iteration. This is where the ILIKE keyword comes in handy.
The SELECT * ILIKE statement allows you to retrieve columns whose names conform to a given string pattern. This is similar to using the ILIKE operator in the where clause, but in this case, it is applied to column names instead of row values. The underscore (_) symbol can be used to match any single character, while the percent sign (%) can be used to match any sequence of zero or more characters. As the ILIKE operator suggests, the pattern matching process is case insensitive.
SELECT * ILIKE '%_1' FROM employee_table;SELECT * REPLACE
You can also use the REPLACE keyword to replace the values of specific columns in a SELECT * statement with an expression that evaluates to the new value. This functionality is especially valuable when you’re using SELECT * to retrieve all columns, yet you want to alter the values of particular columns while preserving the existing column order. In situations where using SELECT * EXCLUDE, followed by new expressions as the old column names, is not the preferred method, the REPLACE feature provides an elegant solution for modifying specific column values within the same SELECT * query.
For example, consider the following employee_table:
+-------------+---------------+------------+------------+
| EMPLOYEE_ID | DEPARTMENT_ID | LAST_NAME | FIRST_NAME |
+-------------+---------------+------------+------------+
| 101 | 1 | Montgomery | Pat |
| 102 | 2 | Levine | Terry |
| 103 | 3 | Comstock | Dana |
+-------------+---------------+------------+------------+The following query:
SELECT * REPLACE ('DEPT-' || department_id AS department_id) FROM employee_table;will yield the following result. Notice that the column department_id remains the second column in the result.
+-------------+---------------+------------+------------+
| EMPLOYEE_ID | DEPARTMENT_ID | LAST_NAME | FIRST_NAME |
+-------------+---------------+------------+------------+
| 101 | DEPT-1 | Montgomery | Pat |
| 102 | DEPT-2 | Levine | Terry |
| 103 | DEPT-3 | Comstock | Dana |
+-------------+---------------+------------+------------+SELECT * Keywords
With the introduction of both keywords, Snowflake has significantly enriched the SELECT * syntax, now comprising four keywords: EXCLUDE, ILIKE, RENAME and REPLACE.
- EXCLUDE: Specifics the columns that should be excluded from the results.
- ILIKE: Retrieves the columns whose names align with a specific pattern. Search is case insensitive.
- REPLACE: Replaces the values of specific columns with expressions that evaluate to new values.
- RENAME: Specifies the column aliases that should be used in the SELECT * results.
Although EXCLUDE and ILIKE cannot be used together in a single SELECT * statement, they can be used individually in combination with other keywords. It’s important to note that these keywords must be arranged in a specific order within the SELECT * statement: EXCLUDE | ILIKE, REPLACE, and then RENAME.
Once again, happy selecting!
For more information on these new features, please visit: SELECT | Snowflake Documentation.
