CODEWITHSUNDEEP
sql
user342957
user342957

Reputation: 31

SQL Statement sorting nvarchar

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?

Upvotes: 3

Views: 122

Answers (4)

AakashM
AakashM

Reputation: 63338

@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

Upvotes: 1

Andomar
Andomar

Reputation: 238106

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.

Upvotes: 2

Anne Sharp
Anne Sharp

Reputation: 119

What about

SELECT * FROM [TABLE] ORDER BY [COLUMN]

Next time, please be a little more specific.

Upvotes: 2

Salil
Salil

Reputation: 47482

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

select name from table_name ORDER By name

Upvotes: 1

Related Questions