Data conversion between the varbinary and varchar types in SQL Server

In industrial control applications, the returned data is often stored in binary format, and the binary data represents a hexadecimal data content every 4 bits.

During parsing, a single byte usually occupies eight digits (BIT). 4 bits at the top indicate a hexadecimal data, and 4 bits at the bottom indicate a hexadecimal data.

 

Problem description: In the SQL Server database, how does one directly convert binary data to string data?

Someone will immediately ask me: Why not?ProgramIs C #, Java, or C ++ used to process this service?

My answer is: I know and have already implemented this. But in the face of some special application environments and business needs (such as data statistics and data analysis), I must do this!

 

In the SQL Server database, convert the varbinary type to the varchar type.

This has been implemented by some people, and it is handled quite cleverly.The implementation idea is as follows:

Each time a byte of data is obtained in varbinary, then 4 BIT data is obtained by dividing by 16, and then 4 BIT data is obtained by MoD 16;

Match the corresponding characters in the following format of the fixed string, and then accumulate them.

CodeAs follows:

     -- ========================================================== ======      --  Function varbin2hexstr      --  Convert varbinary type data to varchar type      --  ========================================================== ======          If   Object_id ( '  DBO. varbin2hexstr  ' ) Is   Not   Null         Drop   Function  DBO. varbin2hexstr  Go          Create   Function  Varbin2hexstr (  @ Bin   Varbinary ( 8000  ))  Returns   Varchar ( 8000  ) As      Begin          Declare   @ Re   Varchar ( 8000 ), @ I   Int          Select   @ Re  =  '' , @ I  =  Datalength ( @ Bin )  While   @ I  >  0              Select   @ Re  =  Substring ( '  0123456789 abcdef  ' , Substring ( @ Bin , @ I ,1 ) /  16  +  1 , 1  )  +  Substring ( '  0123456789 abcdef  ' , Substring ( @ Bin ,@ I , 1 ) %  16  +  1 , 1  )  +  @ Re  ,  @ I  =  @ I  - 1         --  Return ('0x '+ @ Re)          Return   @ Re      End          Go 

Test example:

 
SelectDBO. varbin2hexstr (0x1432fabcdef1234567890)
 

Source: Convert varbinary to a string in sqlserver

 

SQL Server database, implementationVarcharType conversionVarbinaryType

Someone will immediately say: Since the varbinary type has been converted to the varchar type, all you need to do is to reverse implement the function.

If it is so simple, it is estimated that the Internet should have been around for a long time. But the result is: I cannot find it online (at least I cannot find it), and the implementation is not like this!

You can only do it yourself.The implementation idea is as follows:

First, the entire string is grouped by each two digits;

Why is it not grouped by every 8 or 4 bits? This is to take into account the versatility of functions.

If each 8-bit or 4-bit grouping is performed, the entire string must be a multiple of 8 or 4. Otherwise, there will be insufficient characters and the previous value will be supplemented with 0.

Then, each character is processed. For example, the character "a B C D E F" is converted to "10 11 12 13 14 15 ";

Then perform a 16-power operation for every two characters and sum the values. For example, AB = 10*16 of the first party + 11*16 of the second party;

Then, convert the two-character integer sum into a varbinary data with a byte length;

Finally, add all the varbinary data directly.

The Code is as follows:

 --  ========================================================== ======  --  Function hexstr2varbin  -- Convert varchar data to varbinary  --  ========================================================== ======  If   Object_id ( '  DBO. hexstr2varbin  ' ) Is   Not   Null      Drop   Function  DBO. hexstr2varbin  Go Create   Function   [  DBO  ] . [  Hexstr2varbin  ]  (  @ Char   Varchar ( 8000  ))  Returns   Varbinary ( 8000 )  As  Begin      Declare   @ Re   Varbinary ( 8000 ), @ Tempchar   Varchar ( 2  ),  @ Getchar   Varchar ( 1 ), @ Getint  Int , @ N   Int , @ Totalint   Int  ,  @ I   Int , @ Tempint   Int , @ Runnum   Int   --  Number of string CIDR blocks       Select   @ Tempchar =  '' , @ I  =  Datalength ( @ Char ), @ Re  =  0x;  If ( @ I  >  0  )  Begin          If (@ I  %  2   =   0 ) Set   @ Runnum  =   @ I  /  2          Else   Set   @ Runnum  =   @ I  / 2   +   1           While ( @ Runnum   >   0  )  Begin              If ( @ Runnum   =   1 ) Set   @ Tempchar  =   @ Char               Else   Set   @ Tempchar   =   Substring ( @ Char ,( @ Runnum  -  1 ) *  2 , 2  ) Select   @ N  =  1 , @ Totalint  =  0  ;  --  Each string intercepted by loop processing (here the string length is 2)              While   @ N   < ( Datalength ( @ Tempchar )+   1  )  Begin                  Set   @ Getchar  =  Substring ( @ Tempchar , @ N , 1  );  --  Converts a character to a hexadecimal number.                  Select  @ Getint  =  Case                           When   @ Getchar  =  '  A  '   Then   10                          When   @ Getchar  =  '  B  '   Then  11                         When   @ Getchar  =  '  C  '   Then   12                          When   @ Getchar  =  '  D  '   Then   13                        When   @ Getchar  =  '  E  '   Then   14                          When   @ Getchar  =  '  F  '   Then   15                         Else   Convert ( Int , @ Getchar ) End  ;  Set   @ Tempint  =  @ Getint  *  Power ( 16 , Datalength ( @ Tempchar ) - @ N  )  Set   @ Totalint   =   @ Totalint   +   @ Tempint                  Set   @ N  =  @ N  +  1              End                  Set   @ Re =  Convert ( Varbinary ( 1 ), @ Totalint ) +   @ Re  ;  Set   @ Runnum  =  @ Runnum  -  1  ; End      End     Return   @ Re  End  Go 

Test example:

 
SelectDBO. hexstr2varbin ('Ffffff2108f5feefaffae1fff1ffbaf1faffa333333ff3aaafffffffaaff')
 

(End)