SQL Server Convert integer to binary string

Question

I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar.

For example 5 would be converted to "101" and stored as a varchar.

Answer 1

Following could be coded into a function. You would need to trim off leading zeros to meet requirements of your question.

declare @intvalue int
set @intvalue=5

declare @vsresult varchar(64)
declare @inti int
select @inti = 64, @vsresult = ''
while @inti>0
  begin
    select @vsresult=convert(char(1), @intvalue % 2)+@vsresult
    select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1
  end
select @vsresult

Answer 2

declare @i int /* input */
set @i = 42

declare @result varchar(32) /* SQL Server int is 32 bits wide */
set @result = ''
while 1 = 1 begin
  select @result = convert(char(1), @i % 2) + @result,
         @i = convert(int, @i / 2)
  if @i = 0 break
end

select @result

Answer 3

Is it possible to do something similar with a varchar string? What I have is a string that would look something like this

1, 5, 12

and I need to read this cell and convert it to

0001000000100010

to indicate that the 1st, 5th and 12th bits are on. Is there a good way to do this?

Answer 4

Please see this blog post, Converting Integers to Binary Strings, I posted a while back.

Answer 5

declare @intVal Int set @intVal = power(2,12)+ power(2,5) + power(2,1); With ComputeBin (IntVal, BinVal,FinalBin) As ( Select @IntVal IntVal, @intVal %2 BinVal , convert(nvarchar(max),(@intVal %2 )) FinalBin Union all Select IntVal /2, (IntVal /2) %2, convert(nvarchar(max),(IntVal /2) %2) + FinalBin FinalBin From ComputeBin Where IntVal /2 > 0 ) select FinalBin from ComputeBin where intval = ( select min(intval) from ComputeBin);