SQL Statement sorting nvarchar

Question

STD-00-1

STD-00-2

STD-00-10

STD-00-1(T)

STD-00-2(T)

STD-00-10(T)

STD-05-1

STD-05-2

STD-05-10

STD-05-1(T)

STD-05-2(T)

STD-05-10(T)

WIP-00-1

WIP-00-1(T)

What is the best way to achieve this?

Answer 1

What about

SELECT * FROM [TABLE] ORDER BY [COLUMN]

Next time, please be a little more specific.

Answer 2

You'd have to parse the parts and sort. The example query creates fields v1, v2, ... with the data you can sort on. v1 contains STD, v2 contains the first number, v3 is 1 if (T) is present, and v4 is the second number. The outer query then uses those fields to sort, like:

select  YourColumn
from    (
    select  substring(YourColumn,1,3) as v1
    ,       cast(substring(YourColumn,5,2) as int) as v2
    ,       case when YourColumn like '%(T)%' then 1 else 0 end as v3
    ,       cast(replace(substring(YourColumn,8,len(YourColumn)-7),'(T)','') 
                 as int) as v4
    ,       YourColumn
    from    YourTable
) sub
order by v1, v2, v3, v4

For your example data, this returns:

v1  v2  v3  v4  YourColumn     
STD 0   0   1   STD-00-1       
STD 0   0   2   STD-00-2       
STD 0   0   10  STD-00-10      
STD 0   1   1   STD-00-1(T)    
STD 0   1   2   STD-00-2(T)    
STD 0   1   10  STD-00-10(T)   
STD 5   0   1   STD-05-1       
STD 5   0   2   STD-05-2       
STD 5   0   10  STD-05-10      
STD 5   1   1   STD-05-1(T)    
STD 5   1   2   STD-05-2(T)    
STD 5   1   10  STD-05-10(T)   
WIP 0   0   1   WIP-00-1       
WIP 0   1   1   WIP-00-1(T)    

Additional tweaks might be required.