2

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.

enter image description here

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)
4
  • 1
    Simply ordering by ItemNameCode should work... Moreover, you should add 1 to your left statement, not subtract 1 : what you did returns PT although (I guess) you expect PT14
    – irimias
    Aug 24, 2016 at 8:55
  • @irimias No, neither will work. Assuming the example list order the OP provided is correct, it appears that the dashes separate distinct numbers. Just ordering by treats it as a single (longer) number. e.g. PT16-62-71 would be regarded as 166271 and come last, but the OP wants it to be seen as 71 OF 62 OF 16.
    – Ian_H
    Aug 24, 2016 at 11:18
  • Simply ordering by ItemNameCode should working perfectly.
    – M.I.M.F
    Aug 24, 2016 at 18:00
  • @M.I.M.F Still no, as I described, and AMtwo describes in the answer below.
    – Ian_H
    Aug 25, 2016 at 8:29

1 Answer 1

6

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)
7
  • your approach is correct... i cannot get the answer by using ORDER BY ItemName Code. once i tried your answer but i'm gettin an error: "Conversion failed when converting the varchar value 'B22' to data type int."
    – M.I.M.F
    Aug 25, 2016 at 17:44
  • @M.I.M.F it sounds like you have non-numeric (ie, alphanumeric) values in more than just the first segment. Can you confirm that's the case with your data?
    – AMtwo
    Aug 25, 2016 at 18:04
  • @M.I.M.F -- I updated my answer with an alternate ORDER BY, since it sounds like the cast-to-int option doesn't work with all your existing data
    – AMtwo
    Aug 25, 2016 at 18:17
  • Anoter Issue In the order PT06-200, PT07-870-871 and PT09-655 order is correct but my ItemCode like this "PT7-870-871, PT9-655" order getting wrong.. you can see bellow order... PT16-495 PT16-246` PT7-870-871 PT9-655
    – M.I.M.F
    Aug 25, 2016 at 19:37
  • 1
    Depending on your requirements, you may need to follow the same pattern I used to further split your criteria. It sounds like you actually do not want to sort the first segment as a single item, but rather to sort on .... the alpha part, then the numeric part? Or Sort on the first 2 characters, then the rest of the first segment?
    – AMtwo
    Aug 25, 2016 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.