IP address to octets split in TSQL

Split IP addresse into octets in SQL Server

In case you need to handle IP addresses in SQL, they are not much useful as string values. To do anything with them rather then just storing and retrieving (checking the ranges for example) you need to split IP address to segments (octets).

The following is modified method I run into online.

DECLARE @Ip varchar(20) = '192.168.0.1'

SELECT 
CAST(SUBSTRING(@Ip, 1, CHARINDEX('.', @Ip) - 1) as int) AS FirstOctetFrom,
CAST(SUBSTRING(@Ip, CHARINDEX('.', @Ip) + 1,CHARINDEX('.', @Ip, CHARINDEX('.', @Ip) + 1) - CHARINDEX('.', @Ip) - 1) as int) as SecondOctetFrom,
CAST(REVERSE(SUBSTRING(REVERSE(@Ip), CHARINDEX('.', REVERSE(@Ip)) + 1,CHARINDEX('.', REVERSE(@Ip), CHARINDEX('.', REVERSE(@Ip)) + 1) -CHARINDEX('.', REVERSE(@Ip)) - 1)) as int) AS ThirdOctetFrom,
CAST(REVERSE(SUBSTRING(REVERSE(@Ip), 1, CHARINDEX('.', REVERSE(@Ip)) - 1)) as int) as FourthOcetFrom
    

Sql Ip Octets

It is doing the job, but it looks a bit robust and not so easy to debug because of a lot of string manipulations.

Another a bit shorter way to do this is using PARSENAME method from SQL Server 

DECLARE @IpStart varchar(20) = '192.168.0.1'
SELECT  PARSENAME(@IpStart,4) AS FirstOctetFrom,
		PARSENAME(@IpStart,3) AS SecondOctetFrom,
		PARSENAME(@IpStart,2) AS ThirdOctetFrom,
		PARSENAME(@IpStart,1) AS FourthOcetFrom
    

Pulling out the range in a table form is now easy since we have IP splitting code.

DECLARE @IpStart varchar(20) = '192.168.0.1'
Declare @IpEnd varchar(20) = '192.168.0.128'

;WITH IpFrom AS(
SELECT  PARSENAME(@IpStart,4) AS FirstOctetFrom,
		PARSENAME(@IpStart,3) AS SecondOctetFrom,
		PARSENAME(@IpStart,2) AS ThirdOctetFrom,
		PARSENAME(@IpStart,1) AS FourthOcetFrom
),
IpTo AS(
SELECT  PARSENAME(@IpEnd,4) AS FirstOctetTo,
		PARSENAME(@IpEnd,3) AS SecondOctetTo,
		PARSENAME(@IpEnd,2) AS ThirdOctetTo,
		PARSENAME(@IpEnd,1) AS FourthOcetTo
)

SELECT * FROM IpFrom
FULL OUTER JOIN IpTo ON 1=1
    

Sql Ip Range Octets

 

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan Stojanovic is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform includion ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

.NET

read more

JavaScript

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article