#snowflake-cloud-data-platform
Вопрос:
Если ниже запрос выполняется в mssql, я получаю следующий вывод: Запрос:
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
Формат чтения: 0x 00 38 00 01 01 02 00 05 00 00 00 00
Вывод: A B D 0x0038 0x02 0
Приведенная выше функция подстроки принимает два байта для каждого указанного значения индекса, исключая первые два байта «0x» в mssql.
Теперь я пытаюсь добиться того же результата, используя snowflake. Может ли кто-нибудь, пожалуйста, помочь, так как мне трудно понять разделение байта на два, создав функцию.
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 $ ;
Комментарии:
1. Это соглашение SQL Server, SQL Server имеет префикс 0x, обозначающий, что значение является двоичным — Я считаю, что это относится исключительно к SQL Server (поправьте меня, если я ошибаюсь). Значения являются хэш-значениями и не должны храниться в виде varchar — — — должны быть двоичными, где используется половина длины байта, поскольку шестнадцатеричные значения не связаны с сопоставлением.
2. «0x» не является эксклюзивным для SQL Server. он обычно используется для обозначения шестнадцатеричных значений практически везде (не двоичных).
Ответ №1:
У Snowflake нет двоичного литерала, поэтому никакая нотация автоматически не обрабатывает значение как двоичное, как 0x
в SQL Server. Вам всегда нужно привести значение к ДВОИЧНОМУ типу данных, чтобы рассматривать это значение как двоичное.
Кроме того, существует несколько различий в обработке двоичных типов данных между SQL Server и Snowflake:
SUBSTRING
в Snowflake можно обрабатывать только строку- … тогда вторым аргументом
SUBSTRING
должно быть количество символов в шестнадцатеричной строке, а не количество байтов в двоичном формате
- … тогда вторым аргументом
- Snowflake поддерживает шестнадцатеричную строку в качестве двоичного представления, но шестнадцатеричная строка не должна включать
0x
префикс - Снежинка не может преобразовать двоичный код в числа, но может преобразовать шестнадцатеричную строку, используя строку формата » X » в
TO_NUMBER
Основываясь на вышеуказанных различиях, ниже приведен пример запроса, достигающего того же результата, что и ваш запрос SQL Server:
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
;
Он возвращает приведенный ниже результат, который совпадает с вашим запросом:
/*
A B D
0038 02 0
*/
Объяснение:
Поскольку Snowflake не имеет двоичного литерала, а ПОДСТРОКА поддерживает только строку (VARCHAR), любые двоичные манипуляции должны выполняться с шестнадцатеричной строкой VARCHAR.
Итак, в запросе первый SUBSTRING
начинается с 1 и извлекает 4 символа, потому что 1 байт состоит из 2 шестнадцатеричных символов, затем извлечение 2 байтов эквивалентно извлечению 4 шестнадцатеричных символов.
Второй SUBSTRING
начинается с 11, потому что, начиная с 6-го байта, означает игнорирование 5 байтов (= 10 шестнадцатеричных символов) и начиная со следующего шестнадцатеричного символа, который является первым шестнадцатеричным символом 6-го байта (10 1 = 11).
Третий SUBSTRING
такой же, как и второй, начиная с 9-го байта означает игнорирование 8 байтов (= 16 шестнадцатеричных символов) и начиная со следующего шестнадцатеричного символа (16 1 = 17).
Кроме того, для преобразования из шестнадцатеричной строки в числовые типы данных используйте X
символ во втором аргументе «формат» TO_NUMBER
функции cast для анализа строки как набора шестнадцатеричных символов. Один X
символ соответствует одному шестнадцатеричному символу в анализируемой строке. Вот почему я 'XX'
анализировал один байт (2 шестнадцатеричных символа) и 'XXXX'
анализировал 2 байта (4 шестнадцатеричных символа).