Menu
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?
660
Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type. When using CAST , a query can fail if BigQuery is unable to perform the cast. If you want to protect your queries from these types of errors, you can use SAFE_CAST.
Cast(cast(20190101 AS STRING) AS date) FORMAT_DATE("%F",CAST(20190101 AS STRING))
To get the substring between two characters, we will use the REGEXP_SUBSTR() function. This function returns the value of the substring that matches the regular expression, or NULL if no match is found. Note: REGEXP_SUBST() is the regular expression equivalent of SUBSTR() .
LAG. Returns the value of the value_expression on a preceding row. Changing the offset value changes which preceding row is returned; the default value is 1 , indicating the previous row in the window frame. An error occurs if offset is NULL or a negative value.
The BigQuery CONCAT function allows you to combine (concatenate) one more values into a single result. Alternatively, you can use the concatenation operator || to achieve the same output.
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
197
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()
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
