/****** Object: UserDefinedFunction [dbo].[ConvertIPtoLong] Script Date: 11/8/2013 10:39:03 AM ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ConvertIPtoLong] (@IP AS varchar(15)) RETURNS bigint AS BEGIN RETURN (CONVERT(bigint, PARSENAME(@IP,1)) + CONVERT(bigint, PARSENAME(@IP,2)) * 256 + CONVERT(bigint, PARSENAME(@IP,3)) * 65536 + CONVERT(bigint, PARSENAME(@IP,4)) * 16777216) END GO On Fri, Nov 8, 2013 at 9:28 AM, Mark Mears <[email protected]> wrote: > Why don’t you handle this similar to the way Microsoft handles Ranged IP > Boundaries in SCCM? It takes the starting address and ending address and > performs the following conversion on each of the two IP Addresses: > > 1) Each of the four octets is converted to its binary equivalent in > 8 binary digits > > 2) Each of these binary numbers is concatenated in a string to form > a 32-bit binary number > > 3) This 32-bit binary number is then converted to a decimal integer > for storage. > > > > For example the IP Address of 192.168.100.123 would be converted as > follows: > > 1) Each of the four octets is converted to its binary equivalent in > 8 binary digits (11000000.10101000.01100100.01111011) > > 2) Each of these binary numbers is concatenated in a string to form > a 32-bit binary number (11000000101010000110010001111011) > > 3) This 32-bit binary number is then converted to a decimal integer > for storage (3232261243) > > > > You can reverse the process to convert back to an IP Address. > > > > Thanks, > ------------------------------ > > > > *Mark Mears* > > [email protected] <[email protected]%0d> > > Phone: (757) 945-2651 > > > > [image: cid:[email protected]] <http://www.cireson.com/> > > > > [image: cid:[email protected]]<http://twitter.com/teamcireson> > Check out our System Center App Store: www.cireson.com/app-store > > > ------------------------------ > > > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Rajkumar Bhoopathy > *Sent:* Friday, November 8, 2013 7:59 AM > *To:* [email protected] > *Subject:* [mssms] Float to Varchar (RoamingBoundaryIPrange) > > > > Hello All : Please excuse me if this already answered many times in the > forums.. > I would like to convert the float value to varchar of Starting and Ending > IP address from RoamingBoundaryIPrange. Can you please help me with some > samples how to do this.. I'm writing some report to get these data. > > select * from RoamingBoundaryIPrange order by DisplayName > > > Regards / Raj... > > > >
<<image004.jpg>>
<<image002.png>>
