How to Convert an IP Address to IP Number in SQL

The process of converting an IP Address to an IP Number is relatively simple. For example take the IP Address - To convert it to an IP Number you would do the following.

Number = (256*256*256*192) + (256*256*168) + (256*1) + (0)

Unfortunately MS SQL doesn't provide any native Split functionality to split the decimal separated ip address into the small pieces of data that we could easily work with. We thought we were going to have to do some nasty string dissection using Substring and CharIndex and then we stumbled upon the beautiful Parsename function. Apparently this function was originally designed to help developers navigate through the MS SQL naming structure (ie databasename.dbo.tablename). For our purposes it worked great to pull apart our decimal separated string so we could perform calculations on each piece of data.

update IPTable set IPNumber = 
      256 * 256 * 256 * CAST(PARSENAME(@ipaddress, 4) AS float) + 
      256 * 256 * CAST(PARSENAME(@ipaddress, 3) AS float) + 
      256 * CAST(PARSENAME(@ipaddress, 2) AS float) + 
      CAST(PARSENAME(@ipaddress, 1) AS float)

Happy coding!