Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to convert binary to character types in SQL Server. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Problem description: how to achieve direct conversion between binary data and string data in SQL Server database? Someone is bound to immediately ask me: why not use C# or JAVA or C++ in the program to handle this business? My answer is: I know this, and it has been realized. But in the face of some special application environment and business requirements (such as: statistics of data, analysis of data), I have to do this! In SQL Server database, the idea of converting varbinary type to varchar type is to get one byte of data in varbinary each time, then divide by 16 to get high-order 4bit data, and then module 16 to get low-order 4bit data; match the corresponding characters from the following forms in fixed strings, and finally add them up. The code is as follows: the copy code is as follows:-- =-- FUNCTION varbin2hexstr-- convert data of type varbinary to type varchar-- = 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 ABCDEFF) substring (@ bin,@i 1) + substring ('0123456789 ABCDE return substring (@ bin,@i,1) + 1) + @ re, @ i=@i-1-- return (' 0x'+@re) return @ re end GO

Test example: select dbo.varbin2hexstr (0x1432fabcdef1234567890) SQL Server database, to achieve the conversion of varchar type to varbinary type will be immediately thought of: now that you have achieved the conversion of varbinary type to varchar type, the next thing you need to do is to reverse the function. But the realization is not the case! The idea is to first group the entire string by every two bits; why not group it by every 8 bits or every 4 bits? Is to take into account the versatility of the function. If grouped by every 8 bits or every 4 bits, the entire string must be a multiple of 8 or 4, otherwise there will be insufficient characters and 0 before it. Then each character is processed, for example, the character "A B C D E F" is converted to "10 11 12 13 14 15", and then every 2 characters are exponentiated by 16 and summed. For example, the first power of AB = 10 * 16 + the zero power of 11 * 16; then convert the integer sum of the two characters into one byte (Byte) length of varbinary data; finally, all the varbinary data can be added directly. The code is as follows: copy the code as follows:-- =-- FUNCTION hexstr2varbin-- convert data of type varchar to type 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-- select @ tempchar='',@i=datalength (@ char), @ re=0x If (@ I > 0) begin if (@ I% 2 = 0) set @ runNum= @ I else set @ runNum= @ iAccord 2 + 1 while (@ runNum > 0) begin if (@ runNum= 1) set @ tempchar = @ char else set @ tempchar = substring (@ char, (@ runNum-1) * 2) select @ nasty 1 -- Loop each string intercepted (the string length here is 2) while @ n < (datalength (@ tempchar) + 1) begin set @ getchar=substring (@ tempchar,@n,1) -- convert characters to hexadecimal corresponding digits 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 force 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 the code as follows: select dbo.hexstr2varbin ('ffffff2353f5feefaffae1fff1ffbaf1faffa33333333ff3aaafffffffaaff')

This is how to convert binaries to character types in the SQL Server shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.