My BiPolar Users aka Sort a AlphaNumeric Field as if its Numeric

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
BipolarNumbers
FROM SampleData
ORDER 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 BipolarNumbers
FROM SampleData
ORDER BY CAST(LEFT(BipolarNumbers
,CASE PATINDEX('%[^0-9]%', BipolarNumbers)
WHEN 0 THEN LEN(BipolarNumbers)
ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)

Lynn Pettis (1/15/2013)

Please compare the following two pieces of code:


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
    BipolarNumbers
FROM SampleData
ORDER 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 BipolarNumbers
FROM SampleData
ORDER BY CAST(LEFT(BipolarNumbers
    ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) 
        WHEN 0 THEN LEN(BipolarNumbers) 
        ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)

The first is my modification to dwain's code and the second is his code unmodified. On my system they return two different result sets. Which one is correct?