T-SQL UDF: Convert Traditional IP into Numeric IP

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.

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

You can quickly test this by issuing the following query:
SELECT dbo.IPtoNumeric(‘24.0.175.222’) as numericIP

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in Networking, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*