Change Schema while creating table

userITJl1 0
Viewed 59

I have an issue later in my process when I want the append tables with a different Datatypes.

I am creating a new table out of an existing table. One column is the Calenderweek(KW) which was originally a STRING. In order to append my tables later on I need the same datatype for the column.

Is there a way to change the datatype for a column while creating the new table?

CREATE TABLE IF NOT EXISTS
  MyNewTable
  AS(

  SELECT
    Column_1 AS 
    Column_1_alias,
    **KW_ AS KW,**
    
  FROM
    SourceTable);
google-bigquery
1 Answers

What this Query does is that it only grabs the value of the column KW that contains a number, then checks if the STRING value contains a character and removes it from the STRING. Finally it CAST to the desired value type of the column, so it ends as an INT.

CREATE TABLE IF NOT EXISTS
 dataset.MyNewTable
 AS(
 
 SELECT
   Column1 AS
   Column1_alias,
   CAST(REGEXP_REPLACE(KW,'[^0-9^]','') as INT64) as KW_Alias
 FROM
    `project.dataset.source`
        WHERE REGEXP_CONTAINS(KW,'[0-9]')
    );

Another possible solution is to use the function REPLACE instead of REGEXP_REPLACE, to replace the string to a number.

Jose Gutierrez Paliza0
Related