SQL SERVER 16 and 10-in-system conversions

The 16 and 10 transformations encountered in recent industrial control projects are easier to implement in. NET, where no direct conversions are found in SQL Server, especially long numbers that are out of range, that is, unsigned 64-bit integers stored in SQL Server. Many of the methods found on the Web apply only to 32-bit integers and 64-bit positive integers, and 64-bit negative numbers cannot be implemented, and the conversion methods used are recorded.

Implemented indirectly using the varbinary in SQL Server.

16 binary string ext. 10 bigint (0-FFFFFFFFFFFFFFFF):

Because the binary is easier to convert to bigint, we first convert the string to binary varbinary and then to 10.

CREATE  Function [dbo].[ Hextoint] (@s varchar) 
returns bigint
begin
 Declare @result bigint
 set @result =convert (bigint, CONVERT (varbinary, CAST (N ' 0x ' + @s as char), 1)--the simplest and most efficient

way to return
 @result
       

10 into the 16 string (bigint positive negative): the same idea at present, it is easy to convert binary varbinary to string, then 10 to binary binary to 16 string output

CREATE   Function [dbo].[ Inttohex] (@num bigint) 
returns varchar ()  
begin
Declare @num2 varbinary (8), @r varchar (+)
SET @ Num2=convert (varbinary (8), @num)--Convert directly to binary
set @r= dbo.varbin2hexstr (@num2)--binary ext. 16 string return
@r 
End Go
       

CREATE function [dbo]. [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
           
       

Above code test environment win2003+sqlserver2008