SQL Server : sorting letters with number -

i'm not @ sql - below have tried far searching. can see in picture barangayname not in order. can see there 'z , s, , 1' between letter b , brgy 10 , brgy 11 far each other.

select *  dbo.barangay order substring(a.barangayname, patindex('%[0-9]%', a.barangayname), len(a.barangayname)) 

i tried work out you.

let's take following sample data:

declare @table table(id int,name varchar(100)) insert @table values(134,'brgy 1') insert @table values(256,'brgy 100') insert @table values(687,'sample 1 z1') insert @table values(954,'brgy 11 zn9') insert @table values(887,'brgy 10 zn11') insert @table values(785,'brgy 098 xys') insert @table values(785,'zone 2 wer') 

the following select statement:

select * @table order 2 

will result as:

but using following code, custom sorting you,

;with cte as(     select *,     substring(name, 0, charindex(' ', name)) spart,     ltrim(rtrim(substring(name, patindex('%[0-9]%', name), len(name))))+' ' restpart     @table ) select id, name cte  order      spart,     cast(left(restpart, patindex('%[^0-9]%', restpart)) int); 

you can achieve:

does make sense?

edit to fix conversion issue

to avoid conversion issue, replace order by section following:

order      spart,     case         when isnumeric(left(restpart, patindex('%[^0-9]%', restpart)))=1 cast(left(restpart, patindex('%[^0-9]%', restpart)) int)         else 0     end;