Converting from binary string

Question

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

Answer 1

0

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.

answered Mar 20 '10 at 09:03 PM

Rob Farley
5.7k ● 14 ● 17 ● 19

Thanks for the interest Rob, what I'm trying to do is a little different. Think of it this way: I know I need a subnet mask of 20 bits (leaving 12 zeroed bits to the right) How do I get the bigint representation of that number in a more elegant manner than I came up with? Conceptually I can create this with a string of 20 1's and 12 0's then convert that but you're saying don't ever do that

Mar 20 '10 at 09:24 PM Scot Hauder

I need a bigint since sql server will not allow a bitwise operation on 2 binary types--which seems like the natural thing to do. Perhaps you or Remus have the resources to find out why that decision was made by the sql dev team.

Mar 20 '10 at 09:37 PM Scot Hauder

I don't think of a subnet mask as 1s and 0s in binary. I think of it as a power of 2, at which point you knock off the remainder. So, for a /29 (8 addresses), you consider this power(2, 32-29). You can easily construct this as 255.255.255 and 255-8-1.

Mar 20 '10 at 09:45 PM Rob Farley

Oh, and when I was doing a lot of this, I would just leave it as four tinyints, since ordering was still easy, and I could easily apply masks in a variety of ways.

Mar 20 '10 at 09:49 PM Rob Farley

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

Mar 21 '10 at 03:28 AM Scot Hauder

(comments are locked)

Answer 2

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

Answer 3

0

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

answered Mar 22 '10 at 06:22 PM

Peso
1.6k ● 4 ● 6 ● 8

(comments are locked)

Answer 4

0

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 )

answered Mar 20 '10 at 10:03 PM

Mister Magoo
480 ● 3

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

Mar 20 '10 at 10:28 PM Scot Hauder

(comments are locked)

Converting from binary string

Follow this question

Related Questions