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.
Subscribe to:
Post Comments (Atom)
3 comments:
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.
The PARSENAME function you suggested just saved me a lot of time, thankyou!
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.
Post a Comment