一个表,其中有一列Character是nvarch(),如下 ID Char content 1 1 x 2 10 y 3 12 z 4 7 y 5 8 z 我不想把character列改成int类的值,但我又想根据其中的字符进行排序,生成如下的 视图 ID Char content 1 1 x 4 7 y 5 8 z 2 10 y 3 12 z 怎么办?谢谢
declare @T table (ID int, [Char] varchar(10), content varchar(10))
insert @T select 1, '1', 'x'
union all select 2, '10', 'y'
union all select 3, '12', 'z'
union all select 4, '7', 'y'
union all select 5, '8', 'z'
select * from @t order by len([Char]),[Char]
(5 行受影响)
ID Char content
----------- ---------- ----------
1 1 x
4 7 y
5 8 z
2 10 y
3 12 z
create table T(ID int, [Char] varchar(10), content varchar(10))
insert T select 1, '1', 'x'
union all select 2, '10', 'y'
union all select 3, '12', 'z'
union all select 4, '7', 'y'
union all select 5, '8', 'z'
select * from T
order by
case when isnumeric([Char])=1 then cast([Char] as int) end
--result
ID Char content
----------- ---------- ----------
1 1 x
4 7 y
5 8 z
2 10 y
3 12 z