I recently was working a SQL report that listed our SCCM subnet boundaries so that we could start to combine then into larger IP ranges. The issue is that IP addresses and subnets are not sorted properly. In researching a solution, I found a couple but this is the one I like that most.
First, let’s take a look at the unaltered query and the first eight results.
FROM vSMS_Boundary
where vSMS_Boundary.BoundaryType = '0'
order by vSMS_Boundary.Value
Value
30.1.100.0
30.1.112.0
30.1.113.0
30.1.137.0
30.1.40.0
30.1.41.0
30.1.42.0
30.1.43.0
As you can see, 30.1.100.0 is listed before 30.1.40.0
The next step is to break up the subnet into four parts and use those to sort.
PARSENAME(vSMS_Boundary.Value, 4) as part1,
PARSENAME(vSMS_Boundary.Value, 3) as part2,
PARSENAME(vSMS_Boundary.Value, 2) as part3,
PARSENAME(vSMS_Boundary.Value, 1) as part4
FROM vSMS_Boundary
where vSMS_Boundary.BoundaryType = '0'
order by part1, part2, part3, part4
| Value | Part1 | Part2 | Part3 | Part4 |
| 30.1.100.0 | 30 | 1 | 100 | 0 |
| 30.1.112.0 | 30 | 1 | 112 | 0 |
| 30.1.113.0 | 30 | 1 | 113 | 0 |
| 30.1.137.0 | 30 | 1 | 137 | 0 |
| 30.1.40.0 | 30 | 1 | 40 | 0 |
| 30.1.41.0 | 30 | 1 | 41 | 0 |
| 30.1.42.0 | 30 | 1 | 42 | 0 |
| 30.1.43.0 | 30 | 1 | 43 | 0 |
We are getting closer but we still have the sorting issue. This is because SQL sees them as strings and not integers. So I converting the data to TINYINT which is an integer with a range of 0-255. This got the sorting to look correct.
CONVERT(TINYINT, PARSENAME(vSMS_Boundary.Value, 4)) as part1,
CONVERT(TINYINT, PARSENAME(vSMS_Boundary.Value, 3)) as part2,
CONVERT(TINYINT, PARSENAME(vSMS_Boundary.Value, 2)) as part3,
CONVERT(TINYINT, PARSENAME(vSMS_Boundary.Value, 1)) as part4
FROM vSMS_Boundary
where vSMS_Boundary.BoundaryType = '0'
order by part1, part2, part3, part4
| Value | Part1 | Part2 | Part3 | Part4 |
| 30.1.40.0 | 30 | 1 | 40 | 0 |
| 30.1.41.0 | 30 | 1 | 41 | 0 |
| 30.1.42.0 | 30 | 1 | 42 | 0 |
| 30.1.43.0 | 30 | 1 | 43 | 0 |
| 30.1.45.0 | 30 | 1 | 45 | 0 |
| 30.1.46.0 | 30 | 1 | 46 | 0 |
| 30.1.47.0 | 30 | 1 | 47 | 0 |
| 30.1.48.0 | 30 | 1 | 48 | 0 |
Now when I did my report, I only displayed the Value column and hid the columns I used for sorting.