practical RBAR removal: convert integers to binary

Question

practical RBAR removal: convert integers to binary

SQLBOT

SSCrazy Eights

Points: 8014
More actions
September 15, 2009 at 8:03 am

#135018

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

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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?

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

Correct, Lynn.

I'm looking to have an integer input column and varchar output like below

150 -> 10010110

255 -> 11111111

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garadin One Orange Chip Points: 29613 More actions · Answer 5

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 6

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

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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;

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

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

Garadin One Orange Chip Points: 29613 More actions · Answer 10

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 11

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

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jonathan SSC-Insane Points: 20427 More actions · Answer 12

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

practical RBAR removal: convert integers to binary

Cookies on SQLServerCentral