字符串分隔排序,该如何解决

字符串分隔排序

SQL code

   create table aa(id,str)   insert aa values (1,'a,d,c,f')   insert aa values (2,'f,a')

SQL code

declare  @aa table(id int ,str varchar(20))   insert @aa values (1,'a,d,c,f')   insert @aa values (2,'f,a');WITH T AS(       SELECT top 26 ROW_NUMBER() OVER (ORDER BY ID) AS n   FROM SYS.SYSOBJECTS),T2AS(    SELECT str,CHAR(64+N) AS M,N    FROM @aa JOIN T    ON CHARINDEX(CHAR(64+N),str) > 0)SELECT     str,    replace(    (SELECT M as [data()]  FROM T2 WHERE str = T3.str ORDER BY N FOR XML PATH('')    ),' ',',') AS newstrFROM @aa T3/*str                  newstr-------------------- ------------a,d,c,f              A,C,D,Ff,a                  A,F*/
[解决办法]
探讨

SQL codecreatetable aa(id,str)insert aavalues (1,'a,d,c,f')insert aavalues (2,'f,a')

 期望结果
 id       str
 1        a,c,d,f
 2        a,f

[解决办法]
SQL code
declare  @aa table(id int ,str varchar(20))   insert @aa values (1,'a,d,c,f')   insert @aa values (2,'f,a');with t as(    select [az]=char(number)     from master..spt_values     where type='p' and number between 97 and 122),t1 as(    select a.id,t.az     from t,@aa a     where charindex(az,str)>0)select id,stuff((select ','+az from t1 where id=a.id order by az for xml path('')),1,1,'') newstr from t1 a group by id/*id          newstr----------- ----------------1           a,c,d,f2           a,f*/
[解决办法]
SQL code
 create table aa(id int ,str varchar(1000))   insert aa values (1,'a,d,c,f')   insert aa values (2,'f,a')    insert aa values(3,'aaaa,呵呵,哈哈,123')gocreate function f_test(@str varchar(8000))returns varchar(8000)asbegin    declare @ret varchar(8000)    set @ret=''    declare @s xml    select @s=cast('<item><S>'+replace(@str,',','</S></item><item><S>')+'</S></item>' as xml)    declare @tb table(str varchar(8000))    select @ret=@ret+','+str from (select A.x.value('S[1]','varchar(8000)')  as str        from @s.nodes('//item')AS A(x))t order by str    return right(@ret,len(@ret)-1)endgoselect *,dbo.f_test(str) from aagodrop function f_testdrop table aa /*1    a,d,c,f                    a,c,d,f2    f,a                        a,f3    aaaa,呵呵,哈哈,123        123,aaaa,哈哈,呵呵*/