Sorting Alpha Numeric and Numeric data in SQL Server

Sorting Alpha Numeric and Numeric data

I have a varchar2 column in which data is present like :

1a

1b

1c

1

2

2a

2d 

and so on ... and output should be sorted like :

1,1a,1b,2,2c,2d ........ 11,22,23,24d ...

Below Query where I have used PATINDEX function of SQL Server, which is similar to the like operator

Syntax : The Patindex function takes two arguments:

PATINDEX ('%pattern%', exp)

declare @t table

(

v varchar(20)

)

insert @t

values ('1a'),

('1b'),

('1c'),

('1'),

('2'),

('2a'),

('2d '),

('11c '),

('111d '),

('11 '),

('22 '),

('222a '),

('222 '),

('111 '),

('11 ')

select *

from @t

order by left(v,patindex('%[^0-9]%',v + 'x')-1) * 1,v