且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

SQL:如何在所有行中获取列中的所有不同字符

更新时间:2023-12-01 10:36:22

鉴于您的列是 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('');