數據庫環境：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比較好理解，并加了一些注釋，這里不再重復。

(全文完)