Sort data in numeric first and then varchar format

Vishal Patil

i have a table which consist a column as

and i want to sort as

means numeric first and then string and without sort by asc or desc

how to do it please suggest me

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Accepted answer

Hello

For that i think you can use stuff with order by clause as described below :

create table #temp
(
data nvarchar(10)
)
insert into #temp values(45)
insert into #temp values('CC')
insert into #temp values(65)
insert into #temp values(75)
insert into #temp values('AA')
insert into #temp values('BB')
select * from #temp order by (stuff(#temp.data,PATINDEX('%[A-Z]%[a-z]%',#temp.data),1,'')),(stuff(#temp.data,PATINDEX('%[0-9]%',#temp.data),1,''))

Output will be like :

I hope you will find it useful

Thanks