TRIM and Format in bigquery-CodePudding

I am writing a dynamic SQL code in bigquery. I am creating hash value for all columns of all tables as below

Select format('%t', t) from table t;

Now I want to apply TRIM function for my columns. But TRIM after Format is not working. It's not trimming the spaces.

Select Trim(format('%t', t)) from table t;

If I do it before format as below then it gives me error for datatype because the columns have other datatypes than string and byte as well.

Select format('%t', trim(t)) from table t;

I can't apply cast function to each column as there are many columns as well as tables. I can't use concat as it does not work for Null.

Please tell me a solution for this.

CodePudding user response：

Consider below approach

create temp function json_extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));""";
select *,
  ( select string_agg(trim(value), '/')
    from unnest(json_extract_values(replace(to_json_string(t), ':null', ':"_"'))) value 
  ) as HashColumn
from your_table t

if applied to dummy data as below

with your_table as (
  select '    1' A, '2    ' B, null C, 4 D union all 
  select '   12   ', null, '4', 5 
)

output is

which, I hope, is exactly what you are looking for

CodePudding user response：

Meantime, you can apply some extra processing on top of original query to get desired result - as in below example

select *, 
  trim(replace(regexp_replace(format('%t', t), r' *, *| *\)|\( *', '/'), '/NULL/', '/_/'), '/') HashColumn
from your_table t

if applied to sample data

with your_table as (
  select '    1' A, '2    ' B, null C, 4 D union all 
  select '   12   ', null, '4', 5 
)

output is