SQL：如何获取所有行中一列中的所有不同字符

一尘不染

SQL：如何获取所有行中一列中的所有不同字符

sql

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

奖励积分，如果可以在没有光标的情况下完成:)

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

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

字符的不同库存为“ productwigenka123”

阅读 110

2021-03-17

共1个答案

一尘不染

假设您的列是varchar，则意味着它只能在任何代码页上存储0到255之间的字符。如果仅使用32-128
ASCII码范围，则可以简单地一一查看是否有32-128个字符。以下查询在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('');

2021-03-17