x

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

asked Mar 20, 2010 at 06:41 PM in Default

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Mar 20, 2010 at 09:03 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 03:28 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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 )
more ▼

answered Mar 20, 2010 at 10:03 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

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, 2010 at 10:28 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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

answered Mar 22, 2010 at 06:45 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

So, what is the difference in the output?
Mar 22, 2010 at 07:02 PM Peso
set vlsmsuffix = 23
Mar 22, 2010 at 07:12 PM Scot Hauder
So it's really a CDIR vs subnet mask thingy?
Mar 22, 2010 at 07:22 PM Peso
yes, so the user can specify the variable network portion of the address
Mar 22, 2010 at 09:45 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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

answered Mar 22, 2010 at 06:22 PM

Peso gravatar image

Peso
1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x37
x29

asked: Mar 20, 2010 at 06:41 PM

Seen: 2758 times

Last Updated: Mar 21, 2010 at 04:30 AM