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

数据库环境：SQL SERVER2008R2

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

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

A1

A,B,B,C

C,C,D

F,S,S

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

A1

A,B,C

C,D

F,S

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

/*数据准备*/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             '')            ) AS A1       FROM   x2 b       GROUP BY id      ) t

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：将字符串中的重复字符剔除

关键词：