Finding unique characters from a table field for all rows

Question

How to find the number of distinct character used in the field having multiple rows.

For example, if there are two rows having data like abcd and eaafg* then distinct character used are abcdefg*.

Answer 1

Here are questions that may refer to what you are asking:

How do I get distinct characters of string column in mssql?

and

SQL: how to get all the distinct characters in a column, across all rows

Answer 2

Try this one -

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)'
)

---

Example (edited):

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)'
)

---

Have a look a t this solution -

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

Answer 3

With tallys:

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)')