WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)

WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT), substring(BipolarNumbers,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE PATINDEX('%[^0-9]%', BipolarNumbers) end, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE (LEN(BipolarNumbers) - PATINDEX('%[^0-9]%', BipolarNumbers)) + 1 end);WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)

WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT) ,PATINDEX('%[^0-9]%', BipolarNumbers)