Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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"

like image 684
frankadelic Avatar asked May 27 '10 23:05

frankadelic


People also ask

How do I extract unique distinct values from a column in SQL?

The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

How can I get distinct values from multiple columns in SQL?

Select with distinct on all columns of the first query. Select with distinct on multiple columns and order by clause. Count() function and select with distinct on multiple columns.

How do you select distinct from one column?

Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set. Since DISTINCT operates on all of the fields in SELECT's column list, it can't be applied to an individual field that are part of a larger group.

Does distinct apply to all columns SQL?

Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.


2 Answers

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.

like image 116
mdma Avatar answered Sep 18 '22 03:09

mdma


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
like image 43
Michael Buen Avatar answered Sep 20 '22 03:09

Michael Buen