Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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

ORDER BY CONVERT(INT, PARSENAME(Step_Id, 1));
share|improve this answer
    
I can! Thank you :) –  deed02392 Mar 12 '12 at 14:31
    
Would this break with extra separators? –  JNK Mar 12 '12 at 14:32
    
+1 though for an innovative use of that function –  JNK Mar 12 '12 at 14:41

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.

share|improve this answer
    
What exactly is going on here? It seems to do the same as Aaron's answer? –  deed02392 Mar 12 '12 at 14:35
1  
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
    
Interesting. +1 for a more portable solution, but Aaron's is most elegant for me, despite the limitations. Thank you –  deed02392 Mar 12 '12 at 14:42

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
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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