CODEWITHSUNDEEP
sqlcastinggoogle-bigquery
Praxiteles
Praxiteles

Reputation: 6030

What to try to get BigQuery to CAST BYTES to STRING?

BigQuery Standard SQL documentation suggests that BYTE fields can be coerced into STRINGS.

We have a byte field that is the result of SHA256 hashing a field using BigQuery itself.

We now want to coerce it to a STRING, yet when we run "CAST(field_name to STRING)" we get an error:

Query Failed Error: Invalid cast of bytes to UTF8 string

What is preventing us from getting a string from this byte field? Is it surmountable? If so, what is the solution?

Upvotes: 19

Views: 39851

Answers (3)

Vikash Pareek
Vikash Pareek

Reputation: 1181

You can try SAFE_CONVERT_BYTES_TO_STRING() function.

reference: SAFE_CONVERT_BYTES_TO_STRING

Upvotes: 4

Javier Montón
Javier Montón

Reputation: 5716

If you want to see the "traditional" representation of the hash in String, you have to use TO_HEX() function. 

WITH table AS (
 SELECT SHA256('abc') as bytes_field
)
SELECT bytes_field, TO_HEX(bytes_field) as string_field
FROM table

By default in the UI, BigQuery shows you the base64 representation but if you want to compare it with other sha256 function from other language, for example, you have to use TO_HEX()

Upvotes: 9

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below example should show you an idea 

#standardSQL
WITH t AS (
  SELECT SHA256('abc') x
)
SELECT x, TO_BASE64(x)
FROM t

in short - you can use TO_BASE64() for this

Upvotes: 21

Related Questions