How can I turn an IP address held as a string to the four separate integers?

A. Assuming that it is held as a char/varchar column called ip in a table called IpAddress, then the following code fragment will work.

select 

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

from IpAddress

(Code fragment courtesy of Umachandar Jayachandran)