Union, trim and save as a new table in BigQuery

1
votes

I have 3 tables that have exact same schema and I would like to union/join/merge them all into one big table and save it as combinedtable.

Within each of the 3 tables is the same column that contains a string. The string within this column is prefixed with a "'" which I would like to remove.

I concatenated this character to my string in excel to overcome the scientific notation that excel applied to it. Since I have defined that column as a string in my BigQuery schema I no longer need it and would like to remove it.

SQL or its like is not my thing (yet!), could someone give me an idea how I could do this in BigQuery?

I believe I can do a LTRIM on the column to remove the "'" from the start of the string but I don't know how to piece it all together with the union part.

1
google-bigquery

1 Answers

2
votes

Should be something as simple as below 

SELECT 
  REGEXP_REPLACE(troubled_field, "^'", "") AS fixed_field, 
  second_field, 
  third_field
FROM table1, table2, table3

See also more about UNION ALL at FROM clause and about REGEXP_EXTRACT in Regular expression functions

Btw, to see why I use REGEXP_REPLACE vs LTRIM - try below 

SELECT REGEXP_REPLACE("'''abc'def", "^'", ""), LTRIM("'''abc'def", "'")

It can be not that important in your particular case though

Simpler version can be 

SELECT 
  SUBSTR(troubled_field, 2) AS fixed_field, 
  second_field, 
  third_field
FROM table1, table2, table3

Note: this version assumes ALL values in that column starts with "'"
The first version (with REGEXP_REPLACE) is less concurned about this - if some values do not start with "'" they will be just not changed, whereas in second version (with SUBSTR) first character will always be removed even if it is not "'"

See more about SUBSTR function

Use Options section to set destination table and other attributes
enter image description here