Converting IP addresses to bigints in T-SQL

Because I really want to keep up the rhythm of posting at least one code slice a week, here’s a snippet I wrote a long time ago. In our business, we often want to compare ranges of IP addresses, and so we need to convert them from an quad-based notation (x.x.x.x) to an integer basis. This particular method uses the system function in SQL Server called PARSENAME(), which simply splits a period-delimited varchar into it’s seperate components.

Below is how you flip from quad to integer and back; enjoy!


DECLARE @SrcIP varchar(15),
@SrcIPNbr bigint

SET @SrcIP = ’190.10.10.1′

SET @SrcIPNbr = (CONVERT(bigint, PARSENAME(@SrcIP,4))*POWER(256,3))
+ (CONVERT(bigint, PARSENAME(@SrcIP,3))*POWER(256,2))
+ (CONVERT(bigint, PARSENAME(@SrcIP,2)*POWER(256,1)))
+ CONVERT(bigint, PARSENAME(@SrcIP,1))

SELECT @SrcIPNbr

SELECT CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,3))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,2))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,1))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,0))%256)

Share and Enjoy:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks

December 10, 2008 · stuart · 3 Comments
Posted in: SQL

3 Responses

  1. Zar - March 30, 2009

    Hi Stuart,

    I think this is shorter and more efficient:

    CONVERT(varchar(3),(@SrcIPNbr&0xFF000000)/0xFF0000)+’.'+
    CONVERT(varchar(3),(@SrcIPNbr&0xFF0000)/0xFF00)+’.'+
    CONVERT(varchar(3),(@SrcIPNbr&0xFF00)/0xFF)+’.'+
    CONVERT(varchar(3),(@SrcIPNbr&0xFF))

  2. stuart - March 30, 2009

    Hi Zar,

    It’s definitely shorter; I’m not accustomed to thinking in hex, though :)

    Thanks for the suggestion.

  3. boichev - November 4, 2011

    SET @SrcIPNbr = (CONVERT(bigint, isnull(PARSENAME(@SrcIP,4), 0))*POWER(256,3))
    + (CONVERT(bigint, isnull(PARSENAME(@SrcIP,3), 0))*POWER(256,2))
    + (CONVERT(bigint, isnull(PARSENAME(@SrcIP,2), 0)*POWER(256,1)))
    + CONVERT(bigint, isnull(PARSENAME(@SrcIP,1), 0))

    Juat one bit change added with ISNULL fn.

Leave a Reply