SQL Server convert varbinary(16) to binary text

Question

Similar to this question (Sql Server convert integer to binary string, but I would like to convert a varbinary(16) to its text version instead.

I am doing something tragically wrong as the results of my code will attest.

create function GetGuidBinaryString (@value varbinary(16))
returns varchar(128)
as
begin
declare @vsresult varchar(128) 
declare @inti int 
select @inti = 128, @vsresult = '' 
while @inti>0 
begin 
select @vsresult=convert(char(1), @value % 2)+@vsresult 
select @value = convert(int, (@value / 2)), @inti=@inti-1 
end 

return @vsresult
end


create table #values (binvalue varchar(128))

delete from #values

declare @intcount int
select @intcount = 0
while @intcount < 100
begin
    insert into #values select dbo.GetGuidBinaryString(convert(varbinary(16),convert(bigint,2147483640) + @intcount))
    select @intcount = @intcount+1
end


select * from #values

Perhaps there is some implicit conversion I am doing in the function as the function only works correctly for positive integers.

Answer 1

@value % 2 and @value / 2 is doing an implicit conversion.

select @value = convert(int, (@value / 2)) is doing an explicit conversion to int so here you are getting a negative int for the values stored in varbinary(16) that after the division converted to bigint is larger than 2,147,483,647. % for a negative int will give you a -1.

I do not think it is possible to convert varbinary(16) to binary using % and /. They only work on int/bigint etc.

Here is a conversion routine that works for positive bigint values. I do not know what representation you would expect for negative bigint values. Convert your varbinary(16) field to bigint in the call to the function and perhaps it does what you want. I am sure it does not work for all possible values that you can store in a varbinary(16) field.

create function BigIntToBin (@v bigint)
returns varchar(256)
as
begin
    declare @res varchar(256)
    declare @i int

    set @i = 128
    set @res = ''

    while @i > 0
    begin
        if @v % 2 = 0
            set @res = '0' + @res
        else    
            set @res = '1' + @res
        set @v = @v / 2
        set @i = @i - 1
    end
    return @res
end