Converting from binary string

Question

question

Scot Hauder asked Mar 20, '10

Converting from binary string

I recently answered a with question with the code below. My question is, what is the best way to convert a binary [string] to int so I can remove one of the recursive ctes. Eg SELECT CAST('0011' as binary(1)) I am looking for 3 not 48

DECLARE @Subnet varchar(15)
DECLARE @bits int
DECLARE @IP TABLE (IPAddr varchar(15), Running binary(8))

INSERT @IP
SELECT '10.10.19.2', NULL UNION  -- 00001010 00001010 00010011 00000010
SELECT '10.10.10.5', NULL UNION  -- 00001010 00001010 00001010 00000101
SELECT '10.10.11.2', NULL        -- 00001010 00001010 00001011 00000010
SET @Subnet = '10.10.10.0'       -- 00001010 00001010 00001010 00000000
                                 -- 10.10.11.2 is part of the 10.10.10.0 CIDR block
DECLARE @Fun bigint
SET @Fun = CAST(CAST(16777216 as bigint) * PARSENAME(@Subnet, 4) 
                                 + 65536 * PARSENAME(@Subnet, 3) 
                                   + 256 * PARSENAME(@Subnet, 2) 
                                         + PARSENAME(@Subnet, 1) as binary(8))

UPDATE @IP
SET Running = CAST(CAST(16777216 as bigint) * PARSENAME(IPAddr, 4) 
                                    + 65536 * PARSENAME(IPAddr, 3) 
                                      + 256 * PARSENAME(IPAddr, 2) 
                                            + PARSENAME(IPAddr, 1) as binary(8))

-- determine significant bits
;WITH bits AS (
SELECT  i = cast(1 AS bigint), j = 0
UNION ALL
SELECT i * 2, j + 1
FROM bits
WHERE i <= 2147483648 AND @Fun & i = 0
)
SELECT @bits = MIN(32 - j) FROM bits

-- determine subnet mask
DECLARE @Scissors bigint
SELECT @Scissors = 4294967296 - POWER(CAST(2 AS bigint), CAST(32 AS bigint) - @bits)

SELECT @Subnet [Subnet], COUNT(IPAddr) [Count] 
FROM @IP 
WHERE  @Scissors & Running = @Fun

datatypes cast-convert

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-20T21:03:09Z

Rob Farley answered Mar 20, '10

Substring works on binary values without converting to characters, so you can easily work out each portion of the IP address as you need using that. Don't ever put the binary code as a string, it's not worth it.

Of course, if we had unsigned int available, it would be much easier. Binary(4) does the trick though, as this should work:

cast(substring(TheIP,N,1) as tinyint) -- for N = 1 to 4

I haven't tested this today, as I'm writing this on my iPhone.

Edit: The bit you were looking for was the discussion in the comments about power(2,32-N)-1 which is 00..000111.111 - which can be used with power(2,32)-1 to make something which is a subnet mask.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder commented · Mar 21, 2010 at 03:28 AM

Thank you that's what I was looking for! My train of thought was that of calculating the value of the set bits, but I can simply subtract the zeroed bit value. So my answer becomes: power(2,32)-power(2,32-N) obviating the need for the recursive cte. Cheers

1 ·

Answer 2 · 2010-03-20T22:03:59Z

Mister Magoo answered Mar 20, '10

How about this (assuming you have a tally table/view called tally, which has a column called N):

declare @binary_string varchar(8)

set @binary_string='10101011'

select SUM( 
    POWER( 2 , N - 1 ) 
    * 
    SUBSTRING( REVERSE( @binary_string ) , N , 1 ) 
    ) 
from tally 
where N <= LEN( @binary_string )

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder commented · Mar 20, 2010 at 10:28 PM

Thanks MrM, I am doing something similar in the recursive query, just I'm starting from the most significant bit and working to the right, dividing by 2. I guess I was hoping there was a more elegant solution without walking the binary string. Thank you for looking at it

0 ·

Answer 3 · 2010-03-22T18:22:05Z

Peso answered Mar 22, '10

You want simpler?

SELECT      s.Subnet,
        COUNT(*) AS [Items]
FROM        (
            SELECT  1 * PARSENAME(IPAddr, 4) AS a,
                1 * PARSENAME(IPAddr, 3) AS b,
                1 * PARSENAME(IPAddr, 2) AS c,
                1 * PARSENAME(IPAddr, 1) AS d
            FROM    @IP
        ) AS t
INNER JOIN  (
            SELECT  1 * PARSENAME(@Subnet, 4) AS a,
                1 * PARSENAME(@Subnet, 3) AS b,
                1 * PARSENAME(@Subnet, 2) AS c,
                1 * PARSENAME(@Subnet, 1) AS d,
                @Subnet AS Subnet
        ) AS s ON s.a & t.a = s.a
            AND s.b & t.b = s.b
            AND s.c & t.c = s.c
            AND s.d & t.d = s.d
GROUP BY    s.Subnet

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-03-22T18:45:55Z

Scot Hauder answered Mar 22, '10

Thanks Peso, I ended up going with this, it lets the user choose the variable bit mask. The title is a little misleading, I was looking for a way to calculate the bigint value of a bitmask if I knew the number of bits in the mask

DECLARE @Subnet varchar(15) 
DECLARE @bits int 
DECLARE @VLSMSuffix int 
DECLARE @IP TABLE (IPAddr varchar(15), Running binary(8)) 

INSERT @IP 
SELECT '10.10.19.2', NULL UNION  -- 00001010 00001010 00010011 00000010 
SELECT '10.10.10.5', NULL UNION  -- 00001010 00001010 00001010 00000101 
SELECT '10.10.11.2', NULL        -- 00001010 00001010 00001011 00000010 
SET @Subnet = '10.10.10.0'       -- 00001010 00001010 00001010 00000000 
SET @VLSMSuffix = 24             -- # of bits in subnet mask 
                                 -- 10.10.11.2 is part of the 10.10.10.0/23 CIDR block 
DECLARE @Fun bigint 
SET @Fun = CAST(CAST(16777216 as bigint) * PARSENAME(@Subnet, 4)  
                                 + 65536 * PARSENAME(@Subnet, 3)  
                                   + 256 * PARSENAME(@Subnet, 2)  
                                         + PARSENAME(@Subnet, 1) as binary(8)) 

UPDATE @IP 
SET Running = CAST(CAST(16777216 as bigint) * PARSENAME(IPAddr, 4)  
                                    + 65536 * PARSENAME(IPAddr, 3)  
                                      + 256 * PARSENAME(IPAddr, 2)  
                                            + PARSENAME(IPAddr, 1) as binary(8)) 

-- determine subnet mask 
DECLARE @Scissors bigint 
SELECT @Scissors = 4294967296 - POWER(CAST(2 AS bigint), CAST(32 AS bigint) - @VLSMSuffix) 

SELECT @Subnet [Subnet], COUNT(IPAddr) [Count]  
FROM @IP  
WHERE  @Scissors & Running = @Fun

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso commented · Mar 22, 2010 at 07:02 PM

So, what is the difference in the output?

0 ·

Scot Hauder commented · Mar 22, 2010 at 07:12 PM

set vlsmsuffix = 23

0 ·

Peso commented · Mar 22, 2010 at 07:22 PM

So it's really a CDIR vs subnet mask thingy?

0 ·

Scot Hauder commented · Mar 22, 2010 at 09:45 PM

yes, so the user can specify the variable network portion of the address

0 ·

question