# Converting from binary string

 0 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 6.1k ● 13 ● 15 ● 18 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 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. more ▼ answered Mar 20, 2010 at 09:03 PM Rob Farley 5.7k ● 16 ● 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 @IPWHERE @Scissors & Running = @Fun ` more ▼ answered Mar 22, 2010 at 06:45 PM Scot Hauder 6.1k ● 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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 `` more ▼ answered Mar 22, 2010 at 06:22 PM Peso 1.6k ● 5 ● 6 ● 8 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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 ) ` more ▼ answered Mar 20, 2010 at 10:03 PM 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

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

By Email:

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

Topics:

x37
x29

asked: Mar 20, 2010 at 06:41 PM

Seen: 2807 times

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