September 15, 2009 at 8:03 am
I'm new to using the tally table, and I'm sitting here dumbfounded trying to come up with a way (using a tally table) to convert integers to their binary representation.
Does anyone have an example of this so I can learn how it's done?
It will help me solve a couple of problems I've not solved using a tally table yet.
~SQLBOT
Craig Outcalt
September 15, 2009 at 8:13 am
SQLBOT (9/15/2009)
I'm new to using the tally table, and I'm sitting here dumbfounded trying to come up with a way (using a tally table) to convert integers to their binary representation.Does anyone have an example of this so I can learn how it's done?
It will help me solve a couple of problems I've not solved using a tally table yet.
~SQLBOT
So, you are attempting to do this:
0 -> 0
1 -> 1
2 -> 10
3 -> 11 ...
Correct?
September 15, 2009 at 8:46 am
Correct, Lynn.
I'm looking to have an integer input column and varchar output like below
150 -> 10010110
255 -> 11111111
Craig Outcalt
September 15, 2009 at 8:46 am
If you're fixed on using a "tally table," I guess you could try something like this:
DECLARE @IntVal int;
SET @intVal = 54321;
SELECT SUM(POWER(CAST(10 AS bigint),v.number))
FROM master.dbo.spt_values v
WHERE v.type = 'P' AND v.number < 31 AND @IntVal & POWER(2,v.number) 0;
That's not going to work on large integers, though, because it will overflow with bigint; I guess you could chop the int up into pieces, and then reconstitute the result as a varchar.
Personally, I'd use a CTE for this if you're using SQL Server 2005 or later:
DECLARE @IntVal int;
SET @IntVal = 54321;
WITH CTE (IntVal, BinVal, FinalBin) AS
(SELECT @IntVal IntVal, @IntVal % 2 BinVal, CONVERT(varchar(MAX),@IntVal % 2) FinalBin
UNION ALL
SELECT IntVal / 2, (IntVal / 2) % 2, CONVERT(varchar(MAX),(IntVal / 2) % 2) + FinalBin FinalBin
FROM CTE
WHERE IntVal / 2 > 0)
SELECT FinalBin
FROM CTE
WHERE IntVal =
(SELECT MIN(IntVal)
FROM CTE);
--Jonathan
September 15, 2009 at 9:03 am
Nice solution, Jonathan;
only thing i thought is he might want the preceeding zeros for comparison, if he needs the values as flags?
for example the value 2 returns "10",where he might want "00010" or something;
since the max is (2^32)-1, that's like 31 characters or something;
i changed the one line to this:
--old
SELECT FinalBin
FROM CTE
--new
SELECT right('0000000000000000000000000000000' + FinalBin ,31)
FROM CTE
but that's an aweful lot of preceeding zeros. just another option for the original poster
Lowell
September 15, 2009 at 9:04 am
That recursive CTE is probably the coolest piece of code I've seen in quite a while. Makes me realize you can apply them to more than just hierarchies.
September 15, 2009 at 9:10 am
Jonathan,
Brilliant! Thanks.
I like the power and bitmask method of conversion.
That beats the modulus and division method (I think).
I should have specified that I wanted to use a tally table to cross apply another table and get a column of ints converted to binary representations.
I might just have to take your first method and turn it into a scalar function, which I was trying to avoid.
Craig Outcalt
September 15, 2009 at 9:26 am
Garadin (9/15/2009)
That recursive CTE is probably the coolest piece of code I've seen in quite a while. Makes me realize you can apply them to more than just hierarchies.
They are cool in function, but generally speaking their performance tends to be :unsure: IMO
September 15, 2009 at 9:46 am
How about something like this?
create function dbo.IntToBinary (
@IntVal int
)
returns table
return(
SELECT
cast((select '' + cast(case when @IntVal & POWER(2,v.number) = 0 then '0' else '1' end as char(1))--SUM(POWER(CAST(10 AS bigint),v.number))
FROM master.dbo.spt_values v
WHERE v.type = 'P' AND v.number < 31
order by v.number desc
for xml path ('')) as varchar(32)) as BinaryValue)
;
go
set statistics time on;
select
N,
BinaryValue
from
dbo.Tally
cross apply dbo.IntToBinary(N)
where
N between 1 and 100000;
set statistics time off;
September 15, 2009 at 9:49 am
SQLBOT (9/15/2009)
Jonathan,Brilliant! Thanks.
I like the power and bitmask method of conversion.
That beats the modulus and division method (I think).
I should have specified that I wanted to use a tally table to cross apply another table and get a column of ints converted to binary representations.
I might just have to take your first method and turn it into a scalar function, which I was trying to avoid.
You don't need a UDF to use it.
USE AdventureWorks;
go
SELECT e.EmployeeID, REPLACE(STR(SUM(POWER(CAST(10 AS bigint),v.number)),16),' ','0')
FROM HumanResources.Employee e CROSS JOIN master..spt_values v
WHERE v.type = 'P' AND v.number < 17 AND e.EmployeeID & POWER(2,v.number) 0
GROUP BY e.EmployeeID;
Again, this is going to overflow the bigint if the integers get large, but you should be able to chop it up and do it piecemeal.
--Jonathan
September 15, 2009 at 10:03 am
Gabriel P (9/15/2009)
Garadin (9/15/2009)
That recursive CTE is probably the coolest piece of code I've seen in quite a while. Makes me realize you can apply them to more than just hierarchies.They are cool in function, but generally speaking their performance tends to be :unsure: IMO
I've heard that. I'm not entirely convinced though. I'd be interested to test this against the cross applied tally table method.
September 15, 2009 at 10:29 am
Lynn wins!
here's the synthesis that skips the scalar function.
--
--
SELECT
p.number,
cast((SELECT '' + cast(case when p.number & POWER(2,v.number) = 0 then '0' else '1' end as char(1))
FROM master.dbo.spt_values v
WHERE v.type = 'P' AND v.number < 31
ORDER BY v.number desc
for xml path ('')) as varchar(32)) as BinaryValue
FROM master.dbo.spt_values p
WHERE p.type = 'P'
--
--
I'm very grateful for this thread.
It shows quite a few different ways to avoid a loop.
Thanks all!!
~BOT
Craig Outcalt
September 15, 2009 at 1:58 pm
Here's my "piecemeal" solution that works with any positive integer:
DECLARE @IntVal int;
SET @IntVal = 2147483647;
SELECT @IntVal / POWER(2,16), @IntVal % POWER(2,16);
SELECT
REPLACE(STR(ISNULL((SELECT SUM(POWER(CAST(10 AS bigint),h.number))
FROM master.dbo.spt_values h
WHERE h.type = 'P' AND h.number < 16
AND ((@IntVal / POWER(2,16) & POWER(2,h.number)) 0)),0),16),' ','0') +
REPLACE(STR(ISNULL((SELECT SUM(POWER(CAST(10 AS bigint),l.number))
FROM master.dbo.spt_values l
WHERE l.type = 'P' AND l.number < 16
AND ((@IntVal % POWER(2,16) & POWER(2,l.number)) 0)),0),16),' ','0');
I didn't know what was meant by "tally table," and assumed you meant something like master..spt_values, which we used to call a "numbers table."
--Jonathan
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply