34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a1] nvarchar(22))
Insert #T
select N'1' union all
select N'2L' union all
select N'20XL' union all
select N'10S' union all
select N'L码'
Go
--测试数据结束
SELECT *
FROM #T
ORDER BY CASE WHEN PATINDEX('%[0-9]%', [a1]) > 0
THEN CONVERT(INT, SUBSTRING([a1], PATINDEX('%[0-9]%', [a1]),
CASE WHEN PATINDEX('%[0-9][^0-9]%',
[a1]) > 0
THEN PATINDEX('%[0-9][^0-9]%',
[a1])
ELSE LEN(a1)
END - PATINDEX('%[0-9]%', [a1]) + 1))
ELSE 100000
END;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a1] nvarchar(22))
Insert #T
select N'1' union all
select N'2L' union all
select N'20XL' union all
select N'10S' union all
select N'L码'
Go
--测试数据结束
SELECT *
FROM #T
ORDER BY CASE WHEN PATINDEX('%[0-9]%', [a1]) > 0
THEN SUBSTRING([a1], PATINDEX('%[^0-9][0-9]%', [a1]) + 1,
LEN([a1]) - PATINDEX('%[^0-9][0-9]%', [a1]))
ELSE a1
END;