CODEWITHSUNDEEP
google-bigquery
Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

Select all columns, but replace some with expression in Google BigQuery?

Similar to Select All Columns Except Some in Google BigQuery? - we want to SELECT * from the table, but instead of excluding some columns, we want to replace them with some expression. For example, given table with columns: name, start_date, amount, end_date, comment, we want to convert start and end from STRING to DATE. It is possible to write 

SELECT 
  * EXCEPT(start_date, end_date),
  CAST(start_date AS DATE) start_date,
  CAST(end_date AS DATE) end_date

But this would change order of columns moving start and end to the end.

Upvotes: 16

Views: 13887

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

I would go further and say - you can chain EXCEPT and REPLACE in same SELECT
It is not that obvious from documentation, so I thought it valuable

For example

SELECT * EXCEPT(end_date) REPLACE(
  CAST(start_date AS DATE) AS start_date)
FROM T

This will remove end_date from output end replace original start_date with casted to date start_date

Upvotes: 9

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

In addition to SELECT * EXCEPT, Google BigQuery also supports SELECT * REPLACE clause in Standard SQL dialect. Documentation can be found here: https://cloud.google.com/bigquery/sql-reference/query-syntax#select-list Your example will become:

SELECT * REPLACE(
  CAST(start_date AS DATE) AS start_date,
  CAST(end_date AS DATE) AS end_date)
FROM T

Upvotes: 19

Related Questions