sql中一个字段中字符为（3,1,2,7,8），字符类型为varchar，怎样写sql语句可以让字符排序显示呢（1,2,3,7,8）？

[小白619]

各位大神，sql中一个字段中字符为（3,1,2,7,8），字符类型为varchar，怎样写sql语句可以让字符排序显示呢（1,2,3,7,8）？

[唐诗三百首]

create table #t(x varchar(100))

insert into #t(x) select '3,1,2,7,8'

create function dbo.resort(@x varchar(100))
returns varchar(100)
as
begin
 declare @y varchar(100);

 with t as(
 select val=o.value('.','int')
   from (select S=convert(xml,'<r><n>'+replace(@x,',','</n><n>')+'</n></r>')) t
   cross apply S.nodes('/r/n') x(o))
select @y=isnull(@y+',','')+rtrim(val)
 from t
 order by val

 return @y
end


update t
 set t.x=dbo.resort(x)
 from #t t

select * from #t

/*
x
--------------------------
1,2,3,7,8

(1 行受影响)
*/

[小白619]

引用 1 楼 唐诗三百首 的回复:

create table #t(x varchar(100))

insert into #t(x) select '3,1,2,7,8'

create function dbo.resort(@x varchar(100))
returns varchar(100)
as
begin
 declare @y varchar(100);

 with t as(
 select val=o.value('.','int')
   from (select S=convert(xml,'<r><n>'+replace(@x,',','</n><n>')+'</n></r>')) t
   cross apply S.nodes('/r/n') x(o))
select @y=isnull(@y+',','')+rtrim(val)
 from t
 order by val

 return @y
end


update t
 set t.x=dbo.resort(x)
 from #t t

select * from #t

/*
x
--------------------------
1,2,3,7,8

(1 行受影响)
*/

大神，我按照您的写法，发现数据排序后存在重复的情况啊，这个是咋情况呢

[唐诗三百首]

alter function dbo.resort(@x varchar(8000))
returns varchar(8000)
as
begin
 declare @y varchar(8000);
 
 with t as(
 select val=o.value('.','int')
   from (select S=convert(xml,'<r><n>'+replace(@x,',','</n><n>')+'</n></r>')) t
   cross apply S.nodes('/r/n') x(o))
select @y=isnull(@y+',','')+rtrim(val)
 from t
 order by val
 
 return @y
end