CODEWITHSUNDEEP
google-bigqueryformattrim
Kritika
Kritika

Reputation: 21

TRIM in bigquery

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

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.

Please tell me a solution for this.

Upvotes: 0

Views: 1658

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

enter image description here

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

enter image description here

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

Upvotes: 0

Related Questions