ordering strings that ends with numbers correctly

Question

Just curious, is it possible to achieve this ordering:

X5
X30

rather than this resulting from the 'out of the box' ORDER BY:

X30
X5

Look here: http://stackoverflow.com/questions/1250960/how-to-sort-out-numeric-strings-as-numerics/1250989#1250989 — Guffa, May 23 '15 at 10:35
Thanks Guffa. I could do anything like this is C# as well (-: but look for a TSQL solution. Btw, why does item_5 come after file_10 in your answer? — cs0815, May 23 '15 at 12:57
if you have all values like x[number] then just add one more column with date type (int for example) and move number from x there and make order by this new column — Max, May 23 '15 at 17:25
So you want them ordered by string length (ascending)? Just a guess since you offer no explanation and your sample data set is rather small. `order by left( Foo, 1 ), Cast( substring( Foo, 2, 10 ) as Int )`? — HABO, May 23 '15 at 17:27

score 1 · Accepted Answer · answered May 23 '15 at 19:14

Assuming the all values consist of a non-numeric part which may be followed by a numeric part, you can do something like this (using the AdventureWorks2008R2 sample database):

SELECT Name,
    LEFT(Name,LEN(Name)-NULLIF(PATINDEX('%[^0-9]%',REVERSE(Name)),0)+1) AS StringPart,
    CONVERT(FLOAT,RIGHT(Name,NULLIF(PATINDEX('%[^0-9]%',REVERSE(Name)),1))) AS NumericPart
FROM Production.Product
ORDER BY StringPart, NumericPart

This will provide "incorrect" results if the assumptions are not met (for example if the non-numeric part is missing, or if after the numeric part there is another string part, etc).

ordering strings that ends with numbers correctly

1 Answers1