In industrial applications, the returned data is often stored in binary form, and these binary data represent a hexadecimal data content per 4 bit. Parsing, often a byte (byte) occupies 8 bits (bit), high 4bit represents a hexadecimal data, low 4bit represents a hexadecimal data.
problem Description: How do I implement direct conversion between binary data and string data in a SQL Server database?
Someone will ask me immediately: why not use C # or JAVA or C + + in the program to handle this business? My answer is: this I know, also has been achieved. But in the face of some special application environment and business requirements (such as: data statistics, analysis of the data), I must do so!
SQL Server Database, implementing varbinary type conversion to varchar type
The realization of the idea is:
Each time you get a byte of data in the varbinary, and then divide by 16 to get the high 4bit data, and then modulo 16 to get the low 4bit data, respectively, from the fixed string in the form of the following to match the corresponding characters, and finally add up.
The code is as follows:
Copy Code code as follows:
--==============================================
--FUNCTION Varbin2hexstr
--Converts the 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)
SQL Server database, implementing varchar type conversion to varbinary type
Someone must have thought immediately: Now that you have implemented the varbinary type conversion to the varchar type, the next thing you need to do is to reverse the function backwards to implement it. But the realization is not so! The realization of the idea is:
First, the entire string is grouped by two bits;
Why not GROUP by 8-bit or every 4-bit? is to take into account the versatility of the function.
If grouped according to 8 bits or 4 bits, the entire string must be multiples of 8 or 4, otherwise there will be a shortage of characters, preceded by 0.
Each character is then processed, such as: the character "A B C D E F" corresponds to the conversion to "10 11 12 13 14 15";
Then the power of 16 is performed for every 2 characters, and the sum is added. such as: AB = 10 * 16 of the First Party + 11 * 16 of 0 times;
Then converts this 2-character integer and, to a byte (byte) length of varbinary data;
Finally, all the varbinary data can be added directly.
The code is as follows:
Copy Code code as follows:
--==============================================
--FUNCTION Hexstr2varbin
--Converts the data of the varchar type to the varbinary type
--==============================================
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 intercept segments
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;
--loop over each intercepted string (the string length here is 2)
While @n < (datalength (@tempchar) + 1)
Begin
Set @getchar =substring (@tempchar, @n,1);
--Converts a character to a hexadecimal-corresponding 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:
Copy Code code as follows:
Select Dbo.hexstr2varbin (' Ffffff2353f5feefaffae1fff1ffbaf1faffa33333333ff3aaafffffffaaff ')
Finish
This article is from the "Xugang" blog, please be sure to keep this source http://guangrou.blog.51cto.com/404164/1040606
