Nvarchar sort SQL query

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 will have to disassemble the parts and sort them. The sample query creates fields v1, v2, ...with data that you can sort. v1contains STD, v2contains the first number, v3equal to 1 if present (T), and v4- the second number. An external query then uses these fields to sort, for example:

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 settings may be required.

Answer 3

- , sql.

select name from table_name ORDER By name

Answer 4

@Andomar answer, .

, , :

• Separate different parts into separate columns by typing the corresponding data that they actually store.
• to maintain existing functionality, use a new computed column, which is their concatenation, delimited -and cast as needed
• ORDER BY columns separately if you want to sort