10

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.

Community
  • 1
  • 1
Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55

2 Answers2

15

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
Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
6

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

Martin Burch
  • 2,726
  • 4
  • 31
  • 59
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230