MSSQL : Преобразование varbinary в hex строку и обратно

На днях столкнулся с интересной особенностью 1С.
Нужно было передать в 1С двоичные данные (varbinary), но как говорится все проблемы на стыке систем. 1С восприняла данные как объект, каковыми они не являлись. Памятуя о том что 1С любит строковые данные, я начал пытаться преобразовать их в строку. И вот что у меня получилось на выходе:

1. Использование не документированных функций [sys].[fn_varbintohexstr] и [sys].[fn_varbintohexsubstring] Если стоит простое преобразование, то можно воспользоваться [sys].[fn_varbintohexstr] , она принимает только один параметр: бинарную переменную.
   Вот ее полное описание.
   

   
   

   1. CREATE FUNCTION [sys].[fn_varbintohexstr]
   2. (
   3.   @pbinin varbinary(max)
   4. )
   5. returns nvarchar(max)
   6. as
   7. begin
   8.   return sys.fn_varbintohexsubstring(1,@pbinin,1,0)
   9. end

   * This source code was highlighted with Source Code Highlighter.

   Как вы видете функция [sys].[fn_varbintohexstr] является оберткой для [sys].[fn_varbintohexsubstring], и вызывает ее всегда с одними и темиже параметрами.
   [sys].[fn_varbintohexsubstring], в свою очередь имеет более продвинутый функционал. Например, позволяет преобразовывать в строку не всю последовательность, а некую "подстроку". К таким выводам я пришел после просмотра заголовочной части:
   

   
   

   1. CREATE FUNCTION [sys].[fn_varbintohexsubstring] (
   2.    @fsetprefix bit = 1   -- добавлять '0x' в вывод
   3.   ,@pbinin varbinary(max) -- входной binary поток
   4.   ,@startoffset int = 1  -- начальное смещение
   5.   ,@cbytesin int = 0    -- Длина входной части, 0 означает до конца строки
   6. )

   * This source code was highlighted with Source Code Highlighter.

   Остается только проблема обратного преобразования, дело в том что в SQL нет встроенной функции для такого действия. Точнее не было, в MS SQL 2008 есть, но об этом чуть позже.
   Со своими коллегами я договорился что им будут отдавать строку вида 0x0123456789, соответственно и мне они будут отдавать уже не в виде строки, а в виде binary, например так:
   

   
   

   1. INSERT INTO [tab] ([ColBin])
   2. VALUES (0x0123456789)

   * This source code was highlighted with Source Code Highlighter.

   В общем на этом и можно было бы и закончить, если бы не было интересно. Я прекрасно понимал что какой-то выход должен быть, и он был найден.
   
2. Использование XML запроса. Вот сразу табличный пример использования:
   

   
   

   1. ;WITH [cte_d] AS (
   2.   SELECT [ColBin], [sys].[fn_varbintohexstr]([ColBin]) [ColChar] FROM [dbo].[tab]
   3. )
   4. select
   5.   cast('' as xml).value('xs:hexBinary( substring(sql:column("ColChar"), 3) )', 'varbinary(max)') [ColBin_xml]
   6. ,  '0x' + cast('' as xml).value('xs:hexBinary(sql:column("ColBin") )', 'varchar(max)') [ColChar_xml]
   7. ,  [ColBin]
   8. ,  [ColChar]
   9. FROM [cte_d]

   * This source code was highlighted with Source Code Highlighter.

   В CTE выражении я просто готовлю даные, для дальнейшего использования. Самое интересно начинается в SELECT:
   
   • В поле [ColBin_xml] мы плучаем солонку [ColChar] из [cte_d] - команда sql:column("ColChar")
   • Дальше берем подстроку от этой колонки начиная с 3-го знака (отрезаем "0x") - функция substring(,,)
   • Заставляем xml процессор считать полученую строку как hexBinary
   • А в момент возврата данных они интерпретируются как varbinary(max)
   Аналогичным образом работает и прямое преобразование из binary в varchar (поле [ColChar_xml])
   
   • Берем колонку [ColBin] sql:column("ColBin")
   • Заставляем процессор считать ее как hexBinary
   • И возвращаем как varchar(max)
   • И по желанию можно добавить в начало строки "0x"
3. Использование MS SQL 2008 Ура, Microsoft реализовал преобразование в рамках функции convert :)
   Если у Вас MS SQL 2008 и выше, то вы можете воспользоваться функцией convert. В этом случае она будет принимать 2 или 3 аргумента.
   CONVERT ( тип_данных [ ( длина ) ] , выражение [ , стиль ] )
   При конвертировании из бинарного представления в строковый мы можем воспользоваться одним из 3-х стилей
   
   • 0 - преобразование в соответствии с ASCII кодами (используется по умолчанию)
   • 1 - текстовое представление с добавлением "0x" в начале строки, например так "0x0123456789"
   • 2 - текстовое представление без добавленя "0x" в начале строки, например так "0123456789"

   
   

   1. DECLARE @hexbin varbinary(max);
   2. SET @hexbin = 0x48656C6C6F20576F726C64212121;
   3.  
   4. SELECT 1, CONVERT(varchar(MAX), @hexbin)
   5. union
   6. SELECT 2, CONVERT(varchar(MAX), @hexbin,1)
   7. union
   8. SELECT 3, CONVERT(varchar(MAX), @hexbin,2)

   * This source code was highlighted with Source Code Highlighter.

   результат
   

   ----------------------------------------------------
   style       result
   ----------- ----------------------------------------
   0           Hello World!!!
   1           0x48656C6C6F20576F726C64212121
   2           48656C6C6F20576F726C64212121

   При конвертировании в обратную сторону ( из hexString в binary) у нас теже 3 стиля, которые задают условие преобразования.
   

   
   

   1. DECLARE @hexstring varchar(max);
   2. SET @hexstring = '0x48656C6C6F20576F726C64212121';
   3.  
   4. SELECT 0 style, CONVERT(varbinary(MAX), @hexstring) result
   5. union
   6. SELECT 1, CONVERT(varbinary(MAX), @hexstring,1)

   * This source code was highlighted with Source Code Highlighter.

   
   

   1. DECLARE @hexstring varchar(max);
   2. SET @hexstring = '48656C6C6F20576F726C64212121';
   3.  
   4. SELECT 0 style, CONVERT(varbinary(MAX), @hexstring) result
   5. union
   6. SELECT 2, CONVERT(varbinary(MAX), @hexstring,2)

   * This source code was highlighted with Source Code Highlighter.

   результат
   

   -----------------------------------------------------------------------------
   style       result
   ----------- -----------------------------------------------------------------
   0           0x307834383635364336433646323035373646373236433634323132313231
   1           0x48656C6C6F20576F726C64212121

   -----------------------------------------------------------------------------
   style       result
   ----------- -----------------------------------------------------------------
   0           0x34383635364336433646323035373646373236433634323132313231
   2           0x48656C6C6F20576F726C64212121

Замечание! Если при обратном преобразовании строка не будет соответствовать маске (стили 1 и 2) то SQL будет генерировать ошибку.

Msg 8114, Level 16, State 5, Line 1
Ошибка при преобразовании типа данных varchar к varbinary.