将字符串中的重复字符剔除

数据库环境：SQL SERVER2008R2

在网上看到一网友提的需求，要求把字符串列中的重复字符剔除，只保留一个。我简单的把需求描述下，

比如，有一个t表，A1列存字符串，存储的内容如下：

A1

A,B,B,C

C,C,D

F,S,S

剔除重复字符后的结果如下：

A1

A,B,C

C,D

F,S

思路：每一行记录生成一个行号，把字符串中的字符全部存到一列，根据行号和字符去重，然后再用FOR xml PATH合并到一行

/*数据准备*/WITH    x0          AS ( SELECT   1 AS id ,                        'A,B,B,C' AS A1               UNION ALL               SELECT   2 AS id ,                        'C,C,D' AS A1               UNION ALL               SELECT   3 AS id ,                        'F,S,S' AS A1             ),/*将所有字符转存一列，去重*/        x2          AS ( SELECT  DISTINCT                        a.id ,                        SUBSTRING(a.A1, b.number,                                  CHARINDEX(',', a.A1 + ',', b.number)                                  - b.number) AS A1               FROM     x0 a ,                        master..spt_values b               WHERE    b.number >= 1                        AND b.number <= LEN(a.A1)                        AND b.type = 'P'                        AND SUBSTRING(',' + a.A1, b.number, 1) = ','             )     /*根据原先的行号，把行号相同的转回到一行上*/    SELECT  LEFT(A1, LEN(A1) - 1) AS A1    FROM    ( SELECT    id ,                        ( SELECT    a.A1 + ','                          FROM      x2 a                          WHERE     a.id = b.id                        FOR                          XML PATH('')                        ) AS A1              FROM      x2 b              GROUP BY  id            ) t

SQL比较好理解，并加了一些注释，这里不再重复。

(全文完)