Varbinary bytes in Snowflake

user_16595206 0
Viewed 330

If below query is executed in mssql I am getting following output: Query:

select SubString(0x003800010102000500000000,1, 2) as A
,SubString(0x003800010102000500000000, 6, 1) as B
,CAST(CAST(SubString(0x003800010102000500000000, 9, cast(SubString(0x003800010102000500000000, 
 6, 1)As TinyInt)) As VarChar) As Float) as D

Reading Format: 0x 00 38 00 01 01 02 00 05 00 00 00 00

Output: A B D 0x0038 0x02 0

Above substring function is taking two byte for each index value specified excluding the first two bytes "0x" in mssql.

Now I am trying to achieve the same output using snowflake. Can someone pls help as I am difficulty in understanding the byte split into two by creating a function.

CREATE OR REPLACE FUNCTION getFloat1 (p1 varchar) RETURNS Float as $$
Select Case
    WHEN concat(substr(p1::varchar,1, 2),substr(p1::varchar,5, 4)) <> '0x3E00'
        then 0::float
       ELSE 1::float
        //Else substr(p1::varchar, 9, substr(p1::varchar, 6, 1)):: float End as test1 $$ ;
snowflake-cloud-data-platform
1 Answers

Snowflake doesn't have a binary literal, so no notation automatically treats a value as a binary like the 0x notation in SQL Server. You always have to cast a value to the BINARY data type to treat the value as a binary.

Also, there are several differences around the BINARY data type handling between SQL Server and Snowflake:

  • SUBSTRING in Snowflake can handle only a string
    • ... then the second argument of SUBSTRING must be the number of characters as a hex string, not the number of bytes as a binary
  • Snowflake supports a hex string as a representation of a binary, but the hex string must not include the 0x prefix
  • Snowflake has no way to convert a binary to numbers but can convert a hex string by using 'X' format string in TO_NUMBER

Based on the above differences, below is an example query achieving the same result as your SQL Server query:

select
    substring('003800010102000500000000', 1, 4)::binary A,
    substring('003800010102000500000000', 11, 2)::binary B,
    to_number(
      substring(
        '003800010102000500000000', 
        17,
        to_number(substring('003800010102000500000000', 11, 2), 'XX')*2
      ),
      'XXXX'
    )::float D
;

It returns the below result that is the same as your query:

/*
A    B    D
0038 02   0
*/

Explanation:

Since Snowflake doesn't have a binary literal and SUBSTRING only supports a string (VARCHAR), any binary manipulation has to be done with a VARCHAR hex string.

So, in the query, the first SUBSTRING starts from 1 and extracts 4 characters because 1 byte consists of 2 hex characters, then extracting 2 bytes is equivalent to extracting 4 hex characters.

The second SUBSTRING starts from 11 because starting from the 6th byte means ignoring 5 bytes (= 10 hex characters) and starting from the following hex character which is the first hex character of the 6th byte (10 + 1 = 11).

The third SUBSTRING is the same as the second one, starting from the 9th byte means ignoring 8 bytes (= 16 hex characters) and starting from the following hex character (16 + 1 = 17).

Also, to convert from a hex string to numeric data types, using the X character in the second "format" argument of the TO_NUMBER cast function to parse the string as a collection of hex characters. A single X character corresponds to a single hex character in the string to be parsed. That's why I used 'XX' to parse a single byte (2 hex characters) and used 'XXXX' to parse 2 bytes (4 hex characters).

user_170209110
Related