# Natural (numeric) sort on string

I've got a table of data like so:

``````Process_Id  Stage_Id    Step_Id
P1          P1.5        P1.5.1
P1          P1.5        P1.5.10
P1          P1.5        P1.5.11
P1          P1.5        P1.5.12
P1          P1.5        P1.5.13
P1          P1.5        P1.5.14
P1          P1.5        P1.5.15
``````

And as you can see it's not sorting naturally on the last number of the Step (as you or I would imagine it would be sorted in any other situation). This is of course because it's stored as a string.

Is it possible to have these numbers sort naturally? Perhaps ordering by a 'split' pseudo-column consisting of the split of Step_Id by '.'?

Thanks.

-

Assuming you can trust the last octet will always be a number:

``````ORDER BY CONVERT(INT, PARSENAME(Step_Id, 1));
``````
-

If you don't know how many sections you will have but you always want to use the last one numerically, you can do:

`SELECT CAST(RIGHT(Step_id, (CHARINDEX('.',REVERSE(Step_id))-1)) as int)`

You could add this to a `CROSS APPLY` expression and allow it to be used in the `ORDER BY` as well.

-
What exactly is going on here? It seems to do the same as Aaron's answer? – deed02392 Mar 12 '12 at 14:35
Aaron's answer depends on the separator always being a period and always having 4 components or less. If you have 5 components it will return `NULL`. This solution just takes whatever is after your last period and converts it to an int, regardless of how many periods you have. It also allows you to separate with any other character. – JNK Mar 12 '12 at 14:39

Why do you store information in 2 and 3 places (columns)? Why not store it like this (and make the columns integers):

``````Process_Id  Stage_Id    Step_Id
1           5            1
1           5           10
1           5           11
1           5           12
1           5           13
1           5           14
1           5           15
``````

You can always get the previous view by:

``````SELECT
'P' + Process_Id                                   AS Process_Id
, 'P' + Process_Id + '.' + Stage_Id                  AS Stage_Id
, 'P' + Process_Id + '.' + Stage_Id + '.' + Step_Id  AS Step_Id
FROM tableX
``````
-
It drastically simplifies queries later on by being able to determine a step's parents from the step ID itself. – deed02392 Mar 12 '12 at 14:34