Hash column in bigquery-CodePudding

I want to create a hash column on my table. I want to put all values of a row in that column like concatenation and replace null with any symbol. And each cell should be separated with a delimiter. I want to do this on multiple tables so need a dynamic query for this.

for example

A  B    C    D  HashColumn
1  2    Null 4  1/2/_/4
12 Null 4    5  12/_/4/5

Null is getting replaced with _ and delimiter is /

Please tell me how can I do this in bigquery

CodePudding user response：

Try this one:

select IFNULL(CAST(A as string), '_') || '/' 
    || IFNULL(CAST(B as string), '_') || '/' 
    || IFNULL(CAST(C as string), '_') || '/' 
    || IFNULL(CAST(D as string), '_')
from mytable

CodePudding user response：

I would recommend below simple approach

select *, format('%t', t) HashColumn
from your_table t

if applied to sample data in your question - output is

Which looks good enough as a hash for me. But in case if your really need the format you mentioned in the question - you can do some extra string processing - for example

select *, 
  replace(trim(format('%t', t), '()'), 'NULL', '_') HashColumn
from your_table t

in this case - output is