你的位置:首页 > 数据库

[数据库]将字符串中的重复字符剔除


数据库环境: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

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

(全文完)