BigQuery SHA256 function

Question

I need to hash some strings using SHA256. Using BigQuery to do this results in what I understand to be a BASE64 result, where as I need something that is different.

For example, if I want to hash "[email protected]" the result should be:

c392e50ebeca7bea4405e9c545023451ac56620031f81263f681269bde14218b

But doing this in BigQuery:

SELECT SHA256("[email protected]") as sha256;

the result is:

w5LlDr7Ke+pEBenFRQI0UaxWYgAx+BJj9oEmm94UIYs=

It's the first result that I need to get, any ideas if this is possible in BigQuery, I'm trying to avoid needing to use javascript for this.

Answer 1

If you're using Standard SQL in BigQuery then you could use:

SELECT TO_HEX(SHA256("[email protected]")) as sha256;

results:

| sha256     |
| c392e50ebeca7bea4405e9c545023451ac56620031f81263f681269bde14218b |