Tuesday, September 8, 2009

SQL : how to sort ip Address.

ip address, is one of the key point to identify the user. now if your storing ip address on your database with the VARCHAR datatype with length of 25. guess you have 1 lakh's of records of different ip address and you want to sort that.

Then your sorting query will be

select [ipadd] as IpAddress from ip order by [ipadd]


now in this case it will return a result something like this


ipAddress
------------------------
10.0.0.1
192.165.20.2
255.205.2.3
255.145.53.6
64.35.88.9
68.4.5.99
69.65.236.235
98.125.85.36.0


something like that but actually its not your answer , it get sorted on first no. so becasue of that your result get warry.on actual basic the result should come in this format


ipAddress
------------------------
10.0.0.1
64.35.88.9
68.4.5.99
69.65.236.235
98.125.85.36.0
192.165.20.2
255.205.2.3
255.145.53.6


now how to achieve this use some technique here

select [IPAdd] from ip
order by CAST(PARSENAME([IPAdd], 4) AS INT),
CAST(PARSENAME([IPAdd], 3) AS INT),
CAST(PARSENAME([IPAdd], 2) AS INT),
CAST(PARSENAME([IPAdd], 1) AS INT),


now check this you get above result
thank you.

3 comments:

  1. Interesting,cool stuff about the How to sort ip-address i never read like this..Before that i knew only the Ip-address Finding details.I usually check the ip-address in the site ip-details.com and now i got the Ip-address sorting mm rocking.

    ReplyDelete
  2. Stolen from:

    http://www.sql-server-helper.com/tips/sort-ip-address.aspx

    ReplyDelete