Sort numerically when numbers are in the string

- Sort numerically when numbers are in the string

create table #test(col varchar(10)not null primary key)
insert into #test values ('a104')
insert into #test values ('a152')
insert into #test values ('a1010')
insert into #test values ('a15')
insert into #test values ('a17')
insert into #test values ('aa130')
insert into #test values ('b11')
insert into #test values ('b30')
-- usual sort
select * from #test order by col

-- sort on numeric numbers
select * from #test
order by substring (col, 0,patindex('%[0-9]%',col))+ right('00000' + substring (col,patindex('%[0-9]%',col) , datalength(col)),5)
drop table #test
go