8/03/2018

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