在以逗号分隔的文本列中查找唯一值 [英] Find unique values in a column of comma separated text

问题描述

我有一列以逗号分隔的A"列，我想在 A 列中找到所有唯一值.

I have a column "A" that are separated by commas and I want to find all the unique values in Column A.

这是一个非常简短的例子:

Here's a very short example:

Column A
111, 222
333
444
777,999

我想要一个给我以下值的查询:

I want a query which gives me the following value:

Column C
111
222
333
444
777
999

推荐答案

忽略所有评论中提到的表设计的明显问题，并接受这在大表上可能会证明这可能会很慢，这就是我可能会这样做的方法.

Ignoring the obvious problems with your table design as alluded to in all the comments and accepting that this might prove very slow on a huge table here's how I might do it.

首先...我将创建一个语句，将所有行转换为一个巨大的逗号分隔列表.

First... I would create a statement that would turn all the rows into one big massive comma delimited list.

DECLARE @tmp VarChar(max)
SET @tmp = ''
SELECT @tmp = @tmp + ColumnA + ',' FROM TableA

然后使用此 SO 文章中描述的表值 udf 拆分将大量字符串转回带有不同子句的表，以确保它是唯一的.

Then use the table valued udf split described by this SO article to turn that massive string back into a table with a distinct clause to ensure that it's unique.

https://stackoverflow.com/a/2837662/261997

SELECT DISTINCT * FROM dbo.Split(',', @tmp)

这篇关于在以逗号分隔的文本列中查找唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！