Monday, November 5, 2007

T-SQL IP octet split

Found a neat piece of T-SQL that can be used to separate the octets of an IP address:

Declare @IP varchar(20)
Select @IP = '239.147.8.165'
select @IP AS IP,
cast(substring(@IP, 1, charindex('.', @IP) - 1) as int) AS Octet1,
cast(substring(@IP, charindex('.', @IP) + 1,
charindex('.', @IP, charindex('.', @IP) + 1) -
charindex('.', @IP) - 1) as int) as Octet2,
cast(reverse(substring(reverse(@IP), charindex('.', reverse(@IP)) + 1,
charindex('.', reverse(@IP), charindex('.', reverse(@IP)) + 1) -
charindex('.', reverse(@IP)) - 1)) as int) AS Octet3,
cast(reverse(substring(reverse(@IP), 1, charindex('.', reverse(@IP)) - 1)) as int) as Octet4

The source for this is http://www.umachandar.com/technical/SQL6x70Scripts/Main.htm
.

3 comments:

John said...

ran across this one and thought you would enjoy it... try using "ParseName" - it is built into sql server and supports a 4 part identifier to break apart sql object names - which conveniently are the same as IP's... :)

select PARSENAME('10.1.13.14', 1)

will return you the 4th octet.

PaulB said...

The PARSENAME function you suggested just saved me a lot of time, thankyou!

Unknown said...

Thank you for posting. I pulled my hair out trying to get parsename to work where I need to compare IP address octets. For some reason the greater than > and less than < symbols didnt work correctly when splitting octets using parsename. It seems like only the first character is compared.

After much trial and error using parsename I finally stumbled on your post.

Using your method, octets are split correctly and > and < work correctly.