Order IP Addresses
From Wiki
If you have IP addresses in a varchar column and you need to sort them then you can use the parsename function, You will also need to convert each individual part to int or the sorting will be wrong
First create this table
- CREATE TABLE #IpAddresses(IP VARCHAR(15))
- INSERT #IpAddresses VALUES('12.12.12.12')
- INSERT #IpAddresses VALUES('112.12.12.12')
- INSERT #IpAddresses VALUES('12.12.112.12')
- INSERT #IpAddresses VALUES('122.12.12.12')
- INSERT #IpAddresses VALUES('122.122.12.12')
- INSERT #IpAddresses VALUES('122.122.122.12')
- INSERT #IpAddresses VALUES('122.122.122.122')
- INSERT #IpAddresses VALUES('122.122.122.112')
- INSERT #IpAddresses VALUES('122.122.122.123')
Just do a select to see what the data looks like
- SELECT * FROM #IpAddresses
- ORDER BY IP
Output
| 112.12.12.12 |
| 12.12.112.12 |
| 12.12.12.12 |
| 122.12.12.12 |
| 122.122.12.12 |
| 122.122.122.112 |
| 122.122.122.12 |
| 122.122.122.122 |
| 122.122.122.123 |
Let's use parsename without converting to int, as you can see it is not ordered correctly
- SELECT * FROM #IpAddresses
- ORDER BY PARSENAME(IP,4),PARSENAME(IP,3),PARSENAME(IP,2),PARSENAME(IP,1)
Output
| 112.12.12.12 |
| 12.12.112.12 |
| 12.12.12.12 |
| 122.12.12.12 |
| 122.122.12.12 |
| 122.122.122.112 |
| 122.122.122.12 |
| 122.122.122.122 |
| 122.122.122.123 |
When we use convert everything works as expected
- SELECT * FROM #IpAddresses
- ORDER BY CONVERT(INT,PARSENAME(IP,4)),
- CONVERT(INT,PARSENAME(IP,3)),
- CONVERT(INT,PARSENAME(IP,2)),
- CONVERT(INT,PARSENAME(IP,1))
Output
| 12.12.12.12 |
| 12.12.112.12 |
| 112.12.12.12 |
| 122.12.12.12 |
| 122.122.12.12 |
| 122.122.122.12 |
| 122.122.122.112 |
| 122.122.122.122 |
| 122.122.122.123 |
Contributed by: --SQLDenis 02:48, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
