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

Best way is that You should achive in your sql query only.

select name from table_name ORDER By name

Answer 2

What about

SELECT * FROM [TABLE] ORDER BY [COLUMN]

Next time, please be a little more specific.

Answer 3

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.

Answer 4

@Andomar's answer if you can't change the schema.

If however you can change the schema, I would suggest the best thing to do would be:

• separate out the different parts into separate columns, typed appropriately to the data they actually hold
• for maintaining existing functionality, have a new computed column that is their concatenation, with the - separators and casting as appropriate
• ORDER BY the columns separately when you want to sort