从表字段中查找所有行的唯一字符

Question

如何查找多行字段中使用的不同字符数量。

例如，如果有两行数据如abcd和eaafg*，则使用的不同字符是abcdefg*。

- Rajesh Nagda

Answer 1

尝试这个 -

INSERT INTO @temp (txt)
VALUES ('abcd3'), ('abcdefg*')

SELECT disword = (
SELECT DISTINCT dt.ch
FROM (
 SELECT ch = SUBSTRING(t.mtxt, n.number + 1, 1) 
 FROM [master].dbo.spt_values n
 CROSS JOIN (
  SELECT mtxt = (
   SELECT txt
   FROM @temp
   FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'
  )
 ) t
 WHERE [type] = N'p'
  AND number <= LEN(mtxt) - 1
) dt
FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'
)

---

例子（已编辑）：

SET NOCOUNT ON;

DECLARE @temp TABLE (txt VARCHAR(8000))

INSERT INTO @temp (txt)
VALUES ('abcd'), ('abcdefg*'), (REPLICATE('-', 8000)), (REPLICATE('+', 8000))

DECLARE @t TABLE (i BIGINT)

DECLARE 
      @i BIGINT = 1
    , @l BIGINT = (
        SELECT SUM(LEN(txt))
        FROM @temp 
    )

WHILE (@i <= @l) BEGIN

    INSERT INTO @t (i) 
    VALUES (@i), (@i+1), (@i+2), (@i+3), (@i+4), (@i+5), (@i+6), (@i+7), (@i+8), (@i+9)
    SELECT @i += 10

END

SELECT disword = (
    SELECT DISTINCT dt.ch
    FROM (
        SELECT ch = SUBSTRING(t.mtxt, n.i, 1) 
        FROM @t n
        CROSS JOIN (
            SELECT mtxt = (
                SELECT txt
                FROM @temp
                FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'
            )
        ) t
    ) dt
    FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'
)

---

请看这个解决方案 -

SELECT 
      dt.ch
    , cnt = COUNT(1)
FROM (
    SELECT ch = SUBSTRING(t.mtxt, n.i, 1)  
    FROM @t n
    CROSS JOIN (
        SELECT mtxt = (
            SELECT txt
            FROM @temp
            FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)')
    ) t
) dt
WHERE dt.ch != ''
GROUP BY dt.ch
ORDER BY cnt DESC

- Devart

Answer 2

以下是可能与您所询问的相关的问题：

如何在mssql中获取字符串列的不同字符？

和

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

- Inus C

Answer 3

使用计数器：

DECLARE @t TABLE (s NVARCHAR(MAX))

INSERT INTO @t
VALUES ('abcd'), ('abcdefg*')

;WITH tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2(n))

SELECT 
(       SELECT DISTINCT sb
        FROM    tally
        CROSS APPLY ( SELECT SUBSTRING(s, i, 1) sb FROM @t ) ca
        WHERE   sb <> ''
FOR     XML PATH(N'') , TYPE , ROOT).value(N'root[1]', N'NVARCHAR(MAX)')

- Giorgi Nakeuri