Sort Alphanumeric Values with dash sign I have defined a table named Inventory that consists of one varchar(45) field named ItemNameCode.  Here is the table and contains the following alphanumeric values.



Which we want to sort alphanumerically as:

MES40
PT14
PT14-403
PT14-646-649
PT14-646-649
PT15-3-11
PT15-12-42
PT15-12-572-575
PT16-164
PT16-227
PT16-62-71
PT16-136
PT16-137


I tried the bellow query but not getting exact order:

SELECT ItemNameCode
FROM Inventory
ORDER BY LEFT(ItemNameCode,PATINDEX('%[0-9]%',ItemNameCode)-1)
In this case, simply sorting by ItemNameCode doesn't give you the desired sort. Using ORDER BY ItemNameCode would sort the PT15 values like this:

PT15-12-42
PT15-12-572-575
PT15-3-11


But, you expect them sorted like this:

PT15-3-11
PT15-12-42
PT15-12-572-575


Based on your expected results, I would describe the desired sort behavior as "The string is made up of segments separated by dashes. The first segment is alphanumeric, and each subsequent segment is numeric. Sorting should be done by the first segment, then by the second segment, then by the third segment, etc"

To accomplish this, you'll need to essentially break the ItemNameCode into separate calculated columns, and cast the numeric segments to integer before sorting.

This method of splitting the string apart into separate columns is pretty ugly, but works:

SELECT ItemNameCode, c1.seg1, c2.seg2, c3.seg3, c4.seg4
FROM dbo.AlphaNumeric x
OUTER APPLY (SELECT CASE WHEN CHARINDEX('-',x.ItemNameCode,1) = 0 THEN x.ItemNameCode
                        ELSE SUBSTRING(x.ItemNameCode,1,CHARINDEX('-',x.ItemNameCode,1)-1) 
                    END AS seg1,
                    CHARINDEX('-',x.ItemNameCode,1)+1 AS pos1) c1
OUTER APPLY (SELECT CASE WHEN c1.pos1 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c1.pos1) = 0 THEN SUBSTRING(x.ItemNameCode,c1.pos1,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c1.pos1,CHARINDEX('-',x.ItemNameCode,c1.pos1)-(c1.pos1)) 
                    END AS seg2,
                    CASE WHEN c1.pos1 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c1.pos1)+1 
                    END AS pos2) c2
OUTER APPLY (SELECT CASE WHEN c2.pos2 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c2.pos2) = 0 THEN SUBSTRING(x.ItemNameCode,c2.pos2,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c2.pos2,CHARINDEX('-',x.ItemNameCode,c2.pos2)-(c2.pos2)) 
                    END AS seg3,
                    CASE WHEN c2.pos2 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c2.pos2)+1 
                    END AS pos3) c3
OUTER APPLY (SELECT CASE WHEN c3.pos3 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c3.pos3) = 0 THEN SUBSTRING(x.ItemNameCode,c3.pos3,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c3.pos3,CHARINDEX('-',x.ItemNameCode,c3.pos3)-(c3.pos3)) 
                    END AS seg4,
                    CASE WHEN c3.pos3 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c3.pos3)+1 
                    END AS pos4) c4
ORDER BY c1.seg1, CAST(c2.seg2 AS INT), CAST(c3.seg3 AS INT), CAST(c4.seg4 AS INT)


If the 2nd, 3rd, 4th, etc values are not strictly numeric, and you also needed alphanumeric sorting, you could try using this alternate ORDER BY clause on the same query:

ORDER BY c1.seg1, 
        RIGHT('00000' + COALESCE(c2.seg2,''),5),
        RIGHT('00000' + COALESCE(c3.seg3,''),5),
        RIGHT('00000' + COALESCE(c4.seg4,''),5)