# question

## 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)

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

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

FROM @IP
WHERE  @Scissors & Running = @Fun

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

·

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 comment

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

·
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 Like 1 ·

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 comment

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

·
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 Likes 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

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

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)

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

FROM @IP
WHERE  @Scissors & Running = @Fun

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

·
So, what is the difference in the output?
0 Likes 0 ·
·
set vlsmsuffix = 23
0 Likes 0 ·
·
So it's really a CDIR vs subnet mask thingy?
0 Likes 0 ·
·
yes, so the user can specify the variable network portion of the address
0 Likes 0 ·