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

Question

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?

Answer 1

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

Answer 2

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()