How can i order by column with data "01","02","03","1",&quot-CodePudding

I am having SQL table with data as shown below

01  Buy-1
010 Buy-10
011 Buy-11
02  Buy-2
1   Direct-1
10  Direct-10
11  Direct-11
2   Direct-2

I want to order by the data like this

01  Buy-1
02  Buy-2
010 Buy-10
011 Buy-11    
1   Direct-1
2   Direct-2
10  Direct-10
11  Direct-11

CodePudding user response：

This requires a little extra handling of the strings to get the right sorting criteria.

Assuming your data is consistent with your sample data with a hyphen followed by digits you can order by the data to the left of the hyphen and then by casting the first column as an int

select id, data
from t
order by Left(data, nullif(CharIndex('-',data),0)-1),
  Try_Cast(id as int)

CodePudding user response：

This was an intriguing problem and I had to come back and find a solution to this issue as it has been seen many times over the years. When you don't have the leading zeros it is a fairly easy solution but this is different.

This works for your sample data and even some edge cases that I tried. The ordering first effectively counts the number of number of leading zeros and moves that with the most leading zeros to the top. Then it orders by converting the value to an int. This allows you to have more than 1 leading zero and it will still sort correctly. Additionally it will work even if you have character data in the first column.

declare @Something table
(
    SomeValue varchar(10)
    , SomeOtherValue varchar(30)
)

insert @Something
select '01' , 'Buy-1' union all
select '010', 'Buy-10' union all
select '011', 'Buy-11' union all
select '02', 'Buy-2' union all
select '1', 'Direct-1' union all
select '10', 'Direct-10' union all
select '11', 'Direct-11' union all
select '2', 'Direct-2'

select *
from @Something s
order by len(SomeValue) - len(convert(varchar(10), try_convert(int, SomeValue))) desc
    , try_convert(int, SomeValue)

CodePudding user response：

I would first order by your code having a leading zero, and secondly by the code value itself.

select code, description
from MyTable
order by case when left(code,1) = '0' then 0 else 1 end,
         convert(int, code)

CodePudding user response：

Id: 01
_Value: Buy-1

SELECT Id, _Value from tblName order by left(_Value, CHARINDEX('-', _Value)), cast(SUBSTRING(_Value, PATINDEX('%[0-9]%', _Value),len(_Value)) as int)