Data conversion between binary and character 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 use C #, JAVA, or C ++ in the program to process this business? 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.

The implementation idea is as follows:

Each time the data of one byte in varbinary is obtained, then the 4-bit data in the high position is obtained by dividing by 16, and then the 4-bit data in the low position is obtained by MoD 16. The corresponding characters are matched in the following format of the fixed string, and then accumulate it.
The Code is as follows:
Copy codeThe Code is as follows:
-- ===================================================== ========
-- FUNCTION varbin2hexstr
-- Convert data of the varbinary type to the 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 ('0123456789abcdef ', substring (@ bin, @ I, 1)/16 + 1, 1)
+ Substring ('0123456789abcdef ', substring (@ bin, @ I, 1) % 16 + 1, 1)
+ @ Re
, @ I = @ I-1
-- Return ('0x '+ @ re)
Return @ re
End
GO

Test example:

Select dbo. varbin2hexstr (0x1432fabcdef1234567890)

Converts the varchar type to the varbinary type in the SQL Server database.
Some people will immediately think that, since the varbinary type has been converted to the varchar type, what needs to be done next is to reverse implement the function. But this is not the case! 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:
Copy codeThe 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)
Select @ n = 1, @ totalint = 0;
-- Process each intercepted string cyclically (here the string length is 2)
While @ n <(datalength (@ tempchar) + 1)
Begin
Set @ getchar = substring (@ tempchar, @ n, 1 );
-- Converts a character to a number in hexadecimal format.
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:
Copy codeThe Code is as follows:
Select dbo. hexstr2varbin ('ffffff2366f5feefaffae1fff1ffbaf1faffa33333333ff3aaafffffffaaff ')

(End)

This article is from the "xugang" blog, please be sure to keep this source http://guangrou.blog.51cto.com/404164/1040606