sql语句先字母后数字升序排序

select * from members order by cast ( left([membernamepy],patindex('%[^0-9]%',[membernamepy]+',')-1 ) as int)
,[membernamepy] asc

过滤掉字母的

select left([membernamepy],patindex('%[^0-9]%',[membernamepy]+',')-1 ) from members