T-SQL UDF: Convert Traditional IP into Numeric IP

calendar Jan 25, 2007  · Updated: November 1, 2025 · 1 min read  ·
Share on: linkedin bluesky copy

This user-defined function (only available in SQL 2000 and above) is part of a larger project I'm working on. PARSENAME in SQL Server is my new favorite function.. Thanks to Jeff Smith for the heads up.

1CREATE FUNCTION [dbo].[IPtoNumeric] (@strIP varchar(255))
2RETURNS bigint
3AS
4BEGIN
5    DECLARE @intIPNum bigint
6    IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- check to ensure there are 3 dots
7        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint) + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))
8    RETURN @intIPNum
9END

You can quickly test this by issuing the following query:

1SELECT dbo.IPtoNumeric('24.0.175.222') as numericIP