SQL Server Binary Conversion functions

Original: SQL Server binary conversion function

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":

(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:

-- Mode 1 SELECT CONVERT (VARBINARY(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(23785))

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

--Mode 2CREATE FUNCTIONBINARY2HEXSTR (@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)/ -+1,1)                +substring('0123456789ABCDEF',substring(@bin,@i,1)% -+1,1)                +@re            ,@i=@i-1    RETURN('0x'+@re)ENDGO--TestSELECTDbo. BINARY2HEXSTR (23785)

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)

(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:

--Mode 3CREATE FUNCTIONBIGINT2HEXSTR (@value BIGINT)RETURNS VARCHAR( -) asBEGIN    DECLARE @seq CHAR( -)    DECLARE @result VARCHAR( -)    DECLARE @digit CHAR(1)        SET @seq = '0123456789ABCDEF'    --Find the remainder of the decimal @value divided by the remainder, and the value that corresponds to hexadecimal    SET @result = SUBSTRING(@seq, (@value% -)+1,1)     while @value > 0    BEGIN        SET @digit = SUBSTRING(@seq, ((@value/ -)% -)+1,1)        SET @value = @value/ -        IF @value <> 0            SET @result = @digit + @result    END    RETURN @resultENDGO--TestSELECTDbo. BIGINT2HEXSTR (2147483647)SELECTDbo. BIGINT2HEXSTR (2147483648)

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

(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:

--decimal conversion to hexadecimalCREATE FUNCTIONBIGINTTO36HEXSTR (@value BIGINT)RETURNS VARCHAR( -) asBEGIN    DECLARE @seq CHAR( $)    DECLARE @result VARCHAR( -)    DECLARE @digit CHAR(1)        SET @seq = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'    SET @result = SUBSTRING(@seq, (@value% $)+1,1)     while @value > 0    BEGIN        SET @digit = SUBSTRING(@seq, ((@value/ $)% $)+1,1)        SET @value = @value/ $        IF @value <> 0             SET @result = @digit + @result    END     RETURN @resultENDGO--TestSELECTDbo. BIGINTTO36HEXSTR ( *)

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.

Five, Reference Documents

varbinary converted to string in SQL Server

Questions about incrementing a sequence

Binary conversion

SQL 36 binary converted to 10 binary data

Data conversion between varbinary and varchar types is implemented in SQL Server

SQL Server:convert varbinary to varchar

SQL Server Binary Conversion functions