22,210
社区成员
发帖
与我相关
我的任务
分享
;with t
as
(
select 'abc-ef124-汉字' as v union all
select 'abc-ef231' union all
select 'abc-de-fsd023-45'
)
select v,order_str
from
(
select *,
case when v like '%[吖-座]%'
then substring(v,patindex('%[0-9]%',v),
patindex('%[吖-座]%',v) - patindex('%[0-9]%',v)-1)
when v like '%[0-9]%-%[0-9]%'
then substring(v,patindex('%[0-9]%',v),
len(v)-charindex('-',reverse(v))-
patindex('%[0-9]%',v)+1)
when v like '%[0-9]%'
then right(v,len(v)-patindex('%[0-9]%',v)+1)
end as order_str
from t
)tt
order by order_str
/*
v order_str
abc-de-fsd023-45 023
abc-ef124-汉字 124
abc-ef231 231
*/