How do I convert an ip address (stored in a column) to a decimal

Question

Post reply

How do I convert an ip address (stored in a column) to a decimal

myou4511

SSC Rookie

Points: 35
More actions
June 19, 2008 at 2:03 pm

#192550

I am hoping someone can help with some SQL to be able to convert an ip_address stored in a column to a decimal.
I have a column with values like http://www.xxx.yyy.zzz, where each octet maybe w or ww or www.
I need to be able to extract each octet (www, xxx, yyy, zzz) so that I can multiply them by relevant value to obtain a decimal number.
To do this I need to be able do the following computation
(www*256*256*256)+(xxx*256*256)+(yyy*256)+zzz

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

you can do this with charindex or patindex. Search for the "." and then use substring to pull things out.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Or you could take advantage of the fact that an IP address just happens to have four parts just like a fully qualified SQL object name and use PARSENAME. 😀

SELECT PARSENAME('111.222.3.44',4) *256*256*256

+ PARSENAME('111.222.3.44',3) *256*256

+ PARSENAME('111.222.3.44',2) *256

+ PARSENAME('111.222.3.44',1)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

mtassin SSC-Insane Points: 23099 More actions · Answer 3

Jason Selburg (6/19/2008)
Or you could take advantage of the fact that an IP address just happens to have four parts just like a fully qualified SQL object name and use PARSENAME. 😀
SELECT PARSENAME('111.222.3.44',4) *256*256*256
+ PARSENAME('111.222.3.44',3) *256*256
+ PARSENAME('111.222.3.44',2) *256
+ PARSENAME('111.222.3.44',1)

I must say that is beautiful.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Dugi SSCoach Points: 17998 More actions · Answer 4

Jason Selburg (6/19/2008)
Or you could take advantage of the fact that an IP address just happens to have four parts just like a fully qualified SQL object name and use PARSENAME. 😀
SELECT PARSENAME('111.222.3.44',4) *256*256*256
+ PARSENAME('111.222.3.44',3) *256*256
+ PARSENAME('111.222.3.44',2) *256
+ PARSENAME('111.222.3.44',1)

Yes it looks very nice solution!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]