April 10, 2008 at 8:35 am
Does anybody know how to convert in SQL a number from decimal to binary:
Example: 'F' = 1111
I tried select convert(binary, 12.22) but SQL interprets the word 'binary' as Hex.
Thanks a lot!
April 10, 2008 at 8:57 am
Hi Jenny,
I'm less than a newbie in SQL Server. I looked at the documentation and did not find the
type "binary" as you mean "1011111...." ...
I would represent the data in a varchar and create my own functions to convert from
one type to another.
:unsure:
April 10, 2008 at 8:58 am
There's a UDF for converting to any base from base 10 at:
It's not the most efficient code ever, and it's RBAR (inline scalar UDF), but it does work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 10, 2008 at 9:52 am
Thanks for the answer! I tried the function it works well, but one problem is that if the decimal number has any number of decimals it returns the same result, i.e. the following all returns 1100:
select [dbo].[udfConvertBase10NumberToAnyBase](12, 2, 0, 0)
select [dbo].[udfConvertBase10NumberToAnyBase](12.2, 2, 0, 0)
select [dbo].[udfConvertBase10NumberToAnyBase](12.22, 2, 0, 0)
select [dbo].[udfConvertBase10NumberToAnyBase](12.222, 2, 0, 0)
Many thanks!
April 10, 2008 at 11:14 am
You'd have to modify the function to deal with decimals. Probably split the integer portion from the decimal portion and the function separately on the integer, then run it (backwards?) on the fraction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 10, 2008 at 5:04 pm
In your example, you used "12.22". What would you expect 12.22 to look like?
Are you are looking to convert the decimal fraction (22/100) to a binary fraction? .22 (decimal) = .00011100001010001... I gave up the conversion after 17 places, but hopefully you get the point: fractions don't convert to binary very nicely.
In the specific case of the UDF you used, notice that the input variable is an INT, so it drops the decimal portion of your number immediately.
If you are using "12.22" as two integers separated by a ".", as in our normal IP Address notation, then simply use string functions to split it up and send in the integers individually.
GK!
April 11, 2008 at 11:48 am
Thanks! I got another function which works well:
-----------------------------------------
declare @test-2 table(x decimal(18,2))
insert into @test-2
select 12.22 union all
select 9999999999999999.99 union all
select -0.01 union all
select -1.00;
with bits as
( select 7 as n,128 as e union all select 6, 64 union all
select 5, 32 union all select 4, 16 union all select 3, 8 union all
select 2, 4 union all select 1, 2 union all select 0, 1
), bytes as
( select 1 m union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
)
select
x,
-- convert(binary(9), x) as hexValue,
( select
convert(varchar(3), (( convert(tinyint, substring(convert(binary(9), x), m, 1))
& e
)) / e)
as [text()]
from bits
cross join bytes
order by m, n desc
for xml path('')
) as binaryString
from @test-2
April 11, 2008 at 12:00 pm
Another way to do it in function which is better I think:
CREATE FUNCTION udf_bin_me (@IncomingNumber int)
RETURNS varchar(200)
as
BEGIN
DECLARE @BinNumberVARCHAR(200)
SET @BinNumber = ''
WHILE @IncomingNumber <> 0
BEGIN
SET @BinNumber = SUBSTRING('0123456789', (@IncomingNumber % 2) + 1, 1) + @BinNumber
SET @IncomingNumber = @IncomingNumber / 2
END
RETURN @BinNumber
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply