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