SQL:如何在所有行中获取列中的所有不同字符 [英] SQL: how to get all the distinct characters in a column, across all rows

问题描述

在 SQL Server 中是否有一种优雅的方法可以在所有行中查找单个 varchar(50) 列中的所有不同字符?

Is there an elegant way in SQL Server to find all the distinct characters in a single varchar(50) column, across all rows?

如果不用游标就可以加分:)

Bonus points if it can be done without cursors :)

例如，假设我的数据包含 3 行:

For example, say my data contains 3 rows:

productname
-----------
product1
widget2
nicknack3

不同的字符清单将是productwigenka123"

The distinct inventory of characters would be "productwigenka123"

推荐答案

鉴于您的列是 varchar，这意味着它只能在您拥有的任何代码页上存储从代码 0 到 255 的字符.如果你只使用 32-128 ASCII 码范围，那么你可以简单地看看你是否有任何一个 32-128 的字符，一个一个.以下查询执行此操作，在 sys.objects.name 中查找:

Given that your column is varchar, it means it can only store characters from codes 0 to 255, on whatever code page you have. If you only use the 32-128 ASCII code range, then you can simply see if you have any of the characters 32-128, one by one. The following query does that, looking in sys.objects.name:

with cteDigits as (
    select 0 as Number
    union all select 1 as Number
    union all select 2 as Number
    union all select 3 as Number
    union all select 4 as Number
    union all select 5 as Number
    union all select 6 as Number
    union all select 7 as Number
    union all select 8 as Number
    union all select 9 as Number)
, cteNumbers as (
    select U.Number + T.Number*10 + H.Number*100 as Number
    from cteDigits U
    cross join cteDigits T
    cross join cteDigits H)
, cteChars as (
    select CHAR(Number) as Char
    from cteNumbers 
    where Number between 32 and 128)
select cteChars.Char as [*]
from cteChars
cross apply (
    select top(1) *
    from sys.objects
    where CHARINDEX(cteChars.Char, name, 0) > 0) as o
for xml path('');

这篇关于SQL:如何在所有行中获取列中的所有不同字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！