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

Question

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

For example, say my data contains 3 rows:

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

The distinct inventory of characters would be "productwigenka123"

Is the order of the characters important? – Daniel Vassallo May 27 '10 at 23:40 — Daniel Vassallo, May 27 '10 at 23:40
No, the characters can be returned in any order. – frankadelic May 27 '10 at 23:41 — frankadelic, May 27 '10 at 23:41

score 27 · Answer 1 · edited Oct 19 '16 at 14:29

27

Here's a query that returns each character as a separate row, along with the number of occurrences. Assuming your table is called 'Products'

WITH ProductChars(aChar, remain) AS (
   SELECT LEFT(productName,1), RIGHT(productName, LEN(productName)-1) 
      FROM Products WHERE LEN(productName)>0
   UNION ALL
   SELECT LEFT(remain,1), RIGHT(remain, LEN(remain)-1) FROM ProductChars
      WHERE LEN(remain)>0
)
SELECT aChar, COUNT(*) FROM ProductChars
GROUP BY aChar

To combine them all to a single row, (as stated in the question), change the final SELECT to

SELECT aChar AS [text()] FROM
  (SELECT DISTINCT aChar FROM ProductChars) base
FOR XML PATH('')

The above uses a nice hack I found here, which emulates the GROUP_CONCAT from MySQL.

The first level of recursion is unrolled so that the query doesn't return empty strings in the output.

edited Oct 19 '16 at 14:29

user3071284

6,955
6
43
57

answered May 27 '10 at 23:56

mdma

56,943
12
94
128

1

Getting "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – Ricardo stands with Ukraine Nov 28 '17 at 14:13
1

@Riga I correct "Michael Buen" answer to support all cases, regardless of string's length – Mahmoud Moravej Aug 15 '18 at 11:34
I've posted an answer that builds on this one for those of us getting maximum recursion exceptions or encoded characters like `&` – Nathan Aug 18 '20 at 22:13

score 10 · Answer 2 · edited Aug 15 '18 at 11:33

Use this (shall work on any CTE-capable RDBMS):

select x.v into prod from (values('product1'),('widget2'),('nicknack3')) as x(v);

Test Query:

with a as 
(
    select v, '' as x, 0 as n from prod 
    union all
    select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select v, x, n from a -- where n > 0
order by v, n
option (maxrecursion 0)

Final Query:

with a as 
(
    select v, '' as x, 0 as n from prod 
    union all
    select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select distinct x from a where n > 0
order by x
option (maxrecursion 0)

Oracle version:

with a(v,x,n) as 
(
    select v, '' as x, 0 as n from prod 
    union all
    select v, substr(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select distinct x from a where n > 0

score 6 · Accepted Answer · answered May 28 '10 at 01:05

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

Thomas · Answer 4 · 2010-05-28T01:05:16.907

If you have a Numbers or Tally table which contains a sequential list of integers you can do something like:

Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From dbo.Numbers As N
    Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')

If you are using SQL Server 2005 and beyond, you can generate your Numbers table on the fly using a CTE:

With Numbers As
    (
    Select Row_Number() Over ( Order By c1.object_id ) As Value
    From sys.columns As c1
        Cross Join sys.columns As c2
    )
Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From Numbers As N
    Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')

Nathan · Answer 5 · 2020-08-18T22:37:47.950

Building on mdma's answer, this version gives you a single string, but decodes some of the changes that FOR XML will make, like & -> &.

WITH ProductChars(aChar, remain) AS (
   SELECT LEFT(productName,1), RIGHT(productName, LEN(productName)-1) 
      FROM Products WHERE LEN(productName)>0
   UNION ALL
   SELECT LEFT(remain,1), RIGHT(remain, LEN(remain)-1) FROM ProductChars
      WHERE LEN(remain)>0
)
SELECT STUFF((
  SELECT N'' + aChar AS [text()]
  FROM (SELECT DISTINCT aChar FROM Chars) base
  ORDER BY aChar
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 1, N'')
-- Allow for a lot of recursion. Set to 0 for infinite recursion
OPTION (MAXRECURSION 365)

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

5 Answers5

Linked