Binary

Hi,
I have a column that contains binary representation of numbers. For example
'10' for 2 and '11' for 3 and ...
I wanted to write a function to convert this column to INT but I thought
there might be a built-in function in SQL Server.
Are there any function?
Thanks in advance,
LeilaHi
You should be able to cast this directly!
DECLARE @.bin varbinary(10)
SET @.bin = 2
SELECT @.bin, cast(@.bin as int )
John
"Leila" wrote:

> Hi,
> I have a column that contains binary representation of numbers. For exampl
e
> '10' for 2 and '11' for 3 and ...
> I wanted to write a function to convert this column to INT but I thought
> there might be a built-in function in SQL Server.
> Are there any function?
> Thanks in advance,
> Leila
>
>|||Leila,
There is an undocumented system function fn_replbitstringtoint()
that will do this, if you pad your column value by adding '0's on
the left to make it 32 characters long. Here are some examples:
select fn_replbitstringtoint('00000000000000000
000000000000011')
select fn_replbitstringtoint('1')
select fn_replbitstringtoint(right(replicate('0
',32)+'1',32))
declare @.yourBits varchar(32)
set @.yourBits = '1101'
select fn_replbitstringtoint(right(replicate('0
',32)+@.yourBits,32))
Since this function is not documented, it is not supported and
may not exist or work the same way in the future. You should
not use it in production code.
You can write an equivalent function as a user-defined function.
Here's a newsgroup thread that should help:
http://groups.google.co.uk/groups?q...tstring+integer
Steve Kass
Drew University
Leila wrote:

>Hi,
>I have a column that contains binary representation of numbers. For example
>'10' for 2 and '11' for 3 and ...
>I wanted to write a function to convert this column to INT but I thought
>there might be a built-in function in SQL Server.
>Are there any function?
>Thanks in advance,
>Leila
>
>|||John,
Leila has the input string '10'. If she already had the
value 2 as input, she wouldn't need to do this. I don't
think there is any conversion using CAST() that will
produce 2 as the result of CAST('10' as ').
Your example produces the integer 2 in the statement
SET @.bin = 2, not in the CAST() operation, and in order
to do SET @.bin = 2, you must already have the answer.
Leila needs something to fill in the gap here:
declare @.columnValue varchar(32)
set @.columnValue = '10'
declare @.result int
'? -- @.result gets the value 2, without your typing 2
select @.result
SK
John Bell wrote:
>Hi
>You should be able to cast this directly!
>DECLARE @.bin varbinary(10)
>SET @.bin = 2
>SELECT @.bin, cast(@.bin as int )
>John
>"Leila" wrote:
>
>|||I am not sure how I miss-interpreted that!
"Steve Kass" wrote:

> John,
> Leila has the input string '10'. If she already had the
> value 2 as input, she wouldn't need to do this. I don't
> think there is any conversion using CAST() that will
> produce 2 as the result of CAST('10' as ').
> Your example produces the integer 2 in the statement
> SET @.bin = 2, not in the CAST() operation, and in order
> to do SET @.bin = 2, you must already have the answer.
> Leila needs something to fill in the gap here:
> declare @.columnValue varchar(32)
> set @.columnValue = '10'
> declare @.result int
> '? -- @.result gets the value 2, without your typing 2
> select @.result
> SK
> John Bell wrote:
>
>|||Thanks Steve :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:OZckRZrjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Leila,
> There is an undocumented system function fn_replbitstringtoint()
> that will do this, if you pad your column value by adding '0's on
> the left to make it 32 characters long. Here are some examples:
> select fn_replbitstringtoint('00000000000000000
000000000000011')
> select fn_replbitstringtoint('1')
> select fn_replbitstringtoint(right(replicate('0
',32)+'1',32))
> declare @.yourBits varchar(32)
> set @.yourBits = '1101'
> select fn_replbitstringtoint(right(replicate('0
',32)+@.yourBits,32))
> Since this function is not documented, it is not supported and
> may not exist or work the same way in the future. You should
> not use it in production code.
> You can write an equivalent function as a user-defined function.
> Here's a newsgroup thread that should help:
> http://groups.google.co.uk/groups?q...tstring+integer
> Steve Kass
> Drew University
>
> Leila wrote:
>
example|||Why are you doing assembly level programming in SQL? The whole idea of
a database was to separate data from application code with a layer or
two of abstractions between them. You should not be worrying about
things like this.|||Joe,
This is the legacy data and I'm trying to transform it!
Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122068660.835679.173370@.z14g2000cwz.googlegroups.com...
> Why are you doing assembly level programming in SQL? The whole idea of
> a database was to separate data from application code with a layer or
> two of abstractions between them. You should not be worrying about
> things like this.
>|||I would think about getting a low level language like C or something
and move it over to a CSV file that you can edit before it goes into
your SQL data base. The data is probably a mess.