SQL Server Binary Conversion functions

First, background

A friend of the previous time group asked a question: "Add an increment sequence to the query, such as: 0x00000001, that is, each is a 36-digit (0-9,A--Z), and 0x0000000z is followed by 0x00000010, creating a mapping table like the following":

650) this.width=650; "title=" clip_image001 "src=" http://images.cnitblog.com/blog/48305/201409/292130136757343.jpg "alt=" clip_image001 "width=" 467 "height=" 262 "border=" 0 "style=" border:0px;background-image:none;margin:0px; padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure1:)

Second, decimal conversion to hexadecimal

There is a lot of information on the Internet about using SQL statements to convert decimal to 16 binary data, such as:

--Way 1SELECT CONVERT (VARBINARY (50), 23785)

The execution return value is 0x00005ce9, but it should be noted that this should return binary, but the binary estimate is too cumbersome to read, so SQL Server returns 16 binary, if you want to save as a string is not simply put this directly using convert, A SQL similar to the following does not have a return value:

SELECT CONVERT (Varchar,convert (VARBINARY (50), 23785))

So the following function is shown on the net to convert to a 16 binary string:

650 "this.width=650;" src= "http:// Common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none rgb (221,221,221); Background-color:rgb ( 255,255,255); "/>

--Way 2CREATE  function binary2hexstr (@bin  varbinary (8000)) Returns varchar (8000) ASBEGIN     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)                 [email  protected]            ,@[email  Protected]    return (' 0x ' [email protected]) endgo--test select dbo. Binary2hexstr (23785) 

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

The above SQL also returns 0X00005CE9, but this time the return is a string, it seems that the problem has been resolved, but as long as you do not exceed the value of 2147483648, the problem will occur, the execution of the following SQL returned results as shown in Figure2:

SELECT dbo. Binary2hexstr (2147483647) SELECT dbo. Binary2hexstr (2147483648)

650) this.width=650; "title=" clip_image002 "src=" http://images.cnitblog.com/blog/48305/201409/292130144097499.jpg "alt=" clip_image002 "width=" 504 "height=" 156 "border=" 0 "style=" border:0px;background-image:none;margin:0px; padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure2: Data comparison)

Based on the conversion operation of the system, the SQL script written by Song is improved, and the parameter is changed to bigint type:

650 "this.width=650;" src= "http:// Common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none rgb (221,221,221); Background-color:rgb ( 255,255,255); "/>

--Way 3create function bigint2hexstr (@value  bigint) Returns varchar () asbegin     DECLARE  @seq  char     DECLARE  @result  varchar ()      DECLARE  @digit  char (1)         set @ seq =  ' 0123456789ABCDEF '     --the remainder of the decimal @value divided by, finding the remainder corresponding to the hexadecimal value      SET  @result  = substring (@seq,  (@value%16) +1, 1)     while   @value  > 0    begin        set   @digit  = substring (@seq,  (@value/16)%16 +1, 1)          SET  @value  =  @value/16        if @ value <> 0            set @ result =  @digit  +  @result     END    RETURN  @resultENDGO--Test select dbo . Bigint2hexstr (2147483647) select dbo. Bigint2hexstr (2147483648)

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

Execute the above SQL and return the hexadecimal as shown in Figure3:

650) this.width=650; "title=" clip_image003 "src=" http://images.cnitblog.com/blog/48305/201409/292130153007872.jpg "alt=" clip_image003 "width=" 506 "height=" 137 "border=" 0 "style=" border:0px;background-image:none;padding-left:0px; padding-right:0px;padding-top:0px; "/>

(Figure3: Data comparison)

To understand the above function, you need to understand the decimal conversion to 16 binary arithmetic rules, if the decimal number 23785 to hexadecimal, the steps to calculate the formula is:

1. 23785/16=1486 remainder 9, decimal 9 corresponds to hex 9;

2. 1486/16=92, decimal 14 corresponds to the hexadecimal E;

3. 92/16=5, decimal 12 corresponds to the hexadecimal C;

4. 5/16=0 remainder 5, decimal 5 corresponds to hex 5;

Hexadecimal is inverted for the remainder, which is 5ce9, so decimal 23785 = hexadecimal 0x5ce9

Third, decimal conversion to 36 binary

The conversion from decimal to 36 can be supported by the above example modification:

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

--decimal conversion to hexadecimal create function bigintto36hexstr (@value  bigint) Returns varchar () Asbegin     DECLARE  @seq  char ($)     DECLARE  @result   VARCHAR (    DECLARE  @digit  char (1)          SET  @seq  =  ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ '     SET  @result  = substring (@seq,  (@value%36) +1, 1)     WHILE  @value  >  0    BEGIN        SET  @digit  =  substring (@seq,  (@value/36)%36 +1, 1)         set   @value  =  @value/36        if  @value  <>  0             SET  @result  =   @digit  +  @result &NBsp;   end     return  @resultENDGO--Test select dbo. Bigintto36hexstr (35)

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

Execute the above test SQL, return 0xZ, test success;

Four, Additional Information

In fact, it is not necessary to write BINARY2HEXSTR functions because SQL Server provides system functions that support conversion master.dbo.fn_varbintohexstr or Master.dbo.fn_ Varbintohexsubstring, for example:

Select MASTER.DBO.FN_VARBINTOHEXSTR (2147483647) Select Master.dbo.fn_varbintohexsubstring (1,2147483647,1,0)

But they do. There is no limit to the conversion of decimal data beyond 2147483648.

SQL Server Binary Conversion functions