practical RBAR removal: convert integers to binary

  • 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

  • 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?

  • Correct, Lynn.

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

    150 -> 10010110

    255 -> 11111111

  • 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

  • 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!

  • 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]

  • 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.

  • 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

  • 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;

  • 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

  • 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]

  • 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

  • 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