Binary in mssql

[123458585]

Hi

I have problem with binary values in mssql.
I find this script on the web:

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

My problem is that i can't get the value of i as a column.
PLEASE HELP.

 

[bborissov]

That script just convert from decimal to binary, and it works good. What is your problem?
What column?
Why you want to store '101010' in column instead of 42?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[123458585]

My problem is that a want to convert the value of a column that is set in decimal to binary

 

[bborissov]

SELECT CAST(varbinary(max), YourIntegerColumn)
FROM Table

Again I am not sure what you want. Why should yuou convert that value to binary representation because this is what that script do, it only represent the binary, you can't do anything with it


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Boris,

Casting to varbinary will only show you the hex values. I think the OP wants binary values (1's and 0's).

123458585,

Sounds to me like you would be well advised to make a function to convert your values to binary. By using a function, you can convert all your values in such a way that you can return the converted number in a column.

First, create this function.

Create Function [dbo].[ConvertToBinary](@intvalue int)
Returns VarChar(64)
As
Begin

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

Then, you can use it like this...

Select ColumnToConvert, dbo.ConvertToBinary(ColumnToConvert) As BinaryRepresentation
From   SomeTable

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

But I still don't get it WHY???

Why somebody need the binary representation of some integer?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Why somebody need the binary representation of some integer?

There are 10 kinds of people. Those who understand binary notation, and those who do not.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[123458585]

Thanks gmmastros!!!!!!!!!!

 

[bborissov]

Sure, I have a friend who do calculations faster in HEX then in decimals
He always know what bit is set when you tell him some number

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.