Parse IP address

I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.

I am wondering what would be the best method of doing this, or if anyone has an example.

Thank youlook at SUBSTRING and CHARINDEX in books online and think about how you could nest them.|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:

,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Thanks|||Holy non-standard usage Batman! :shocked:

Just goes to show what a motivated user will do (and drive the developers crazy in the process).

Kudos to you for the unorthodox approach. Hope it works.

Regards,

hmscott|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:


,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Lol - that is brilliant.
There have been a few discussions on how to store IP addresses (char, 4 tinyints, integer etc). This is a new way on me to split the human-friendly form though!|||yup it works great! Def the easiest way to do it :)|||if you have a string with more parts, try this one:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033