CODEWITHSUNDEEP
snowflake-cloud-data-platform
linux
linux

Reputation: 157

How convert a string to binary in Snowflake?

Original byte value in base16 BINARY format -

7e-c2-8c-c2-8a-c7-61-60-34-b7-0e-de-c2-28-9e-b3-08-a6-c3-93-74-99-34-09-48-0c-60-e3-96-47-04-e3

After converting it to VARCHAR - 7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3

I need to convert it back to BINARY and for this I used the TO_BINARY('7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3') function,

but it returns 7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3

instead of the desired 7e-c2-8c-c2-8a-c7-61-60-34-b7-0e-de-c2-28-9e-b3-08-a6-c3-93-74-99-34-09-48-0c-60-e3-96-47-04-e3.

Note - TO_BINARY(string,'base16') doesn't work

Upvotes: 0

Views: 1806

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

You want to strip the hyphens, and use TO_BINARY on 'HEX' input type

SELECT 
    '7e-c2-8c-c2-8a-c7-61-60-34-b7-0e-de-c2-28-9e-b3-08-a6-c3-93-74-99-34-09-48-0c-60-e3-96-47-04-e3' as tb_b16_str
    ,replace(tb_b16_str, '-') as no_hyphen
    ,to_binary(no_hyphen, 'HEX') as now_as_bin
    ,base64_encode(now_as_bin) as now_as_base64
;
TB_B16_STR NO_HYPHEN NOW_AS_BIN NOW_AS_BASE64
7e-c2-8c-c2-8a-c7-61-60-34-b7-0e-de-c2-28-9e-b3-08-a6-c3-93-74-99-34-09-48-0c-60-e3-96-47-04-e3 7ec28cc28ac7616034b70edec2289eb308a6c39374993409480c60e3964704e3 7ec28cc28ac7616034b70edec2289eb308a6c39374993409480c60e3964704e3 fsKMworHYWA0tw7ewiieswimw5N0mTQJSAxg45ZHBOM=

if we take the BASE64, and paste that into notepad++ and BASE64 decode that, and save that as a bin file and open in a hex editor, we can see sure enough now_as_bin is correct decoded as expected..

BASE64 to Binary and externally inspected to show it all worked

You already have a binary:

So thinking about this more, you are already successfully coverting the data to a Snowflake binary:

SELECT '7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3' as hex_str
    ,system$typeof(hex_str)
    ,TO_BINARY(hex_str) as now_as_binary
    ,system$typeof(now_as_binary);

The result you don't like the look of, it's a Snowflake binary.

HEX_STR SYSTEM$TYPEOF(HEX_STR) NOW_AS_BINARY SYSTEM$TYPEOF(NOW_AS_BINARY)
7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3 VARCHAR(64)[LOB] 7ec28cc28ac7616034b70edec2289eb308a6c39374993409480c60e3964704e3 BINARY(8388608)[LOB]

How to export back to teradata:

Or you are really looking for "how to convert a binary to Teradata formatting string for exporting from Snowflake to Teradata"

So the step broken down:

SELECT 
    '7EC28CC28AC7' as hex_str
    ,TO_BINARY(hex_str) as now_as_binary
    ,hex_encode(now_as_binary) as back_to_hex
    ,REGEXP_REPLACE(back_to_hex, '(..)','\\1-') as made_chunky
    ,RTRIM(made_chunky, '-') as wanted
    ;

gives:

HEX_STR NOW_AS_BINARY BACK_TO_HEX MADE_CHUNKY WANTED
7EC28CC28AC7 7ec28cc28ac7 7EC28CC28AC7 7E-C2-8C-C2-8A-C7- 7E-C2-8C-C2-8A-C7

and that can be mashed together like:

SELECT 
    TO_BINARY('7EC28CC28AC7') as now_as_binary
    ,RTRIM(REGEXP_REPLACE(hex_encode(now_as_binary), '(..)','\\1-'), '-') as made_chunky
    ;
NOW_AS_BINARY MADE_CHUNKY
7ec28cc28ac7 7E-C2-8C-C2-8A-C7

Upvotes: 1

Related Questions