Checkout http://sqlmag.com/t-sql/sorting-ip-addresses which has several methods of accomplishing it. Hopefully one will work for you.
Chad Simmons | Microsoft System Center Configuration Manager Consultant | linkedin.com/in/chadsimmons From: [email protected] [mailto:[email protected]] On Behalf Of Kehl Reto Sent: Wednesday, July 29, 2015 9:23 AM To: [email protected] Subject: [mssms] SQL guru needed... need sql query for ip addresses sorted ascending but can't get the 4th octet SELECT IPAddress0, DNSHostName0 FROM v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE (v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0 = 1) ORDER BY CAST(SUBSTRING(IPAddress0, 1, CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) - CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - 1) AS tinyint) --CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) + 1, 3) AS tinyint) the last CAST(SUBSTRING(.. gives error when activated. I can't get it, way too complicated for me.. anybody?
