{"id":87,"date":"2018-02-18T06:09:15","date_gmt":"2018-02-18T06:09:15","guid":{"rendered":"http:\/\/michaelschultz.net\/tech\/?p=87"},"modified":"2018-06-07T06:36:02","modified_gmt":"2018-06-07T06:36:02","slug":"getting-ip-or-subnet-to-sort-correctly-in-a-sql-query","status":"publish","type":"post","link":"https:\/\/michaelschultz.net\/tech\/getting-ip-or-subnet-to-sort-correctly-in-a-sql-query\/","title":{"rendered":"Getting IP or subnet to sort correctly in a SQL query"},"content":{"rendered":"

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.<\/p>\n

First, let\u2019s take a look at the unaltered query and the first eight results.<\/p>\n

SELECT<\/span> vSMS_Boundary.Value
\nFROM vSMS_Boundary
\nwhere<\/span> vSMS_Boundary.BoundaryType =<\/span> '0'<\/span>
\norder by vSMS_Boundary.Value<\/div><\/div>\n

Value
\n30.1.100.0
\n30.1.112.0
\n30.1.113.0
\n30.1.137.0
\n30.1.40.0
\n30.1.41.0
\n30.1.42.0
\n30.1.43.0<\/p>\n

As you can see, 30.1.100.0 is listed before 30.1.40.0<\/p>\n

The next step is to break up the subnet into four parts and use those to sort.<\/p>\n

SELECT<\/span> vSMS_Boundary.Value,<\/span>
\nPARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 4<\/span>)<\/span> as part1,<\/span>
\nPARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 3<\/span>)<\/span> as part2,<\/span>
\nPARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 2<\/span>)<\/span> as part3,<\/span>
\nPARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 1<\/span>)<\/span> as part4
\nFROM vSMS_Boundary
\nwhere<\/span> vSMS_Boundary.BoundaryType =<\/span> '0'<\/span>
\norder by part1,<\/span> part2,<\/span> part3,<\/span> part4<\/div><\/div>\n\n\n\n\n\n\n\n\n\n\n\n
Value<\/td>\nPart1<\/td>\nPart2<\/td>\nPart3<\/td>\nPart4<\/td>\n<\/tr>\n
30.1.100.0<\/td>\n30<\/td>\n1<\/td>\n100<\/td>\n0<\/td>\n<\/tr>\n
30.1.112.0<\/td>\n30<\/td>\n1<\/td>\n112<\/td>\n0<\/td>\n<\/tr>\n
30.1.113.0<\/td>\n30<\/td>\n1<\/td>\n113<\/td>\n0<\/td>\n<\/tr>\n
30.1.137.0<\/td>\n30<\/td>\n1<\/td>\n137<\/td>\n0<\/td>\n<\/tr>\n
30.1.40.0<\/td>\n30<\/td>\n1<\/td>\n40<\/td>\n0<\/td>\n<\/tr>\n
30.1.41.0<\/td>\n30<\/td>\n1<\/td>\n41<\/td>\n0<\/td>\n<\/tr>\n
30.1.42.0<\/td>\n30<\/td>\n1<\/td>\n42<\/td>\n0<\/td>\n<\/tr>\n
30.1.43.0<\/td>\n30<\/td>\n1<\/td>\n43<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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.<\/p>\n

SELECT<\/span> vSMS_Boundary.Value,<\/span>
\nCONVERT(<\/span>TINYINT,<\/span> PARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 4<\/span>)<\/span>)<\/span> as part1,<\/span>
\nCONVERT(<\/span>TINYINT,<\/span> PARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 3<\/span>)<\/span>)<\/span> as part2,<\/span>
\nCONVERT(<\/span>TINYINT,<\/span> PARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 2<\/span>)<\/span>)<\/span> as part3,<\/span>
\nCONVERT(<\/span>TINYINT,<\/span> PARSENAME(<\/span>vSMS_Boundary.Value,<\/span> 1<\/span>)<\/span>)<\/span> as part4
\nFROM vSMS_Boundary
\nwhere<\/span> vSMS_Boundary.BoundaryType =<\/span> '0'<\/span>
\norder by part1,<\/span> part2,<\/span> part3,<\/span> part4<\/div><\/div>\n\n\n\n\n\n\n\n\n\n\n\n
Value<\/td>\nPart1<\/td>\nPart2<\/td>\nPart3<\/td>\nPart4<\/td>\n<\/tr>\n
30.1.40.0<\/td>\n30<\/td>\n1<\/td>\n40<\/td>\n0<\/td>\n<\/tr>\n
30.1.41.0<\/td>\n30<\/td>\n1<\/td>\n41<\/td>\n0<\/td>\n<\/tr>\n
30.1.42.0<\/td>\n30<\/td>\n1<\/td>\n42<\/td>\n0<\/td>\n<\/tr>\n
30.1.43.0<\/td>\n30<\/td>\n1<\/td>\n43<\/td>\n0<\/td>\n<\/tr>\n
30.1.45.0<\/td>\n30<\/td>\n1<\/td>\n45<\/td>\n0<\/td>\n<\/tr>\n
30.1.46.0<\/td>\n30<\/td>\n1<\/td>\n46<\/td>\n0<\/td>\n<\/tr>\n
30.1.47.0<\/td>\n30<\/td>\n1<\/td>\n47<\/td>\n0<\/td>\n<\/tr>\n
30.1.48.0<\/td>\n30<\/td>\n1<\/td>\n48<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now when I did my report, I only displayed the Value column and hid the columns I used for sorting.<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4,26,6],"tags":[],"class_list":["post-87","post","type-post","status-publish","format-standard","hentry","category-configmgr","category-queries","category-reports"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p98a2r-1p","post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/87","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":9,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"predecessor-version":[{"id":102,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/87\/revisions\/102"}],"wp:attachment":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}