Count column comma delimited values oracle

Question

Is it possible to count and also group by comma delimited values in the oracle database table? This is a table data example:

id | user | title | 
1  | foo  | a,b,c |
2  | bar  | a,d   |
3  | tee  | b     |

The expected result would be:

title | count
a     | 2
b     | 2
c     | 1
d     | 1

I wanted to use concat like this:

SELECT a.title FROM Account a WHERE concat(',', a.title, ',') LIKE 'a' OR concat(',', a.title, ',') LIKE 'b' ... GROUP BY a.title?

But I'm getting invalid number of arguments on concat. The title values are predefined, therefore I don't mind if I have to list all of them in the query. Any help is greatly appreciated.

Answer 1

This uses simple string functions and a recursive sub-query factoring and may be faster than using regular expressions and correlated joins:

Oracle Setup:

CREATE TABLE account ( id, "user", title ) AS
  SELECT 1, 'foo', 'a,b,c' FROM DUAL UNION ALL
  SELECT 2, 'bar', 'a,d'   FROM DUAL UNION ALL
  SELECT 3, 'tee', 'b'     FROM DUAL;

Query:

WITH positions ( title, start_pos, end_pos ) AS (
  SELECT title,
         1,
         INSTR( title, ',', 1 )
  FROM   account
UNION ALL
  SELECT title,
         end_pos + 1,
         INSTR( title, ',', end_pos + 1 )
  FROM   positions
  WHERE  end_pos > 0
),
items ( item ) AS (
  SELECT CASE end_pos
         WHEN 0
         THEN SUBSTR( title, start_pos )
         ELSE SUBSTR( title, start_pos, end_pos - start_pos )
         END
  FROM   positions
)
SELECT item,
       COUNT(*)
FROM   items
GROUP BY item
ORDER BY item;

Output:

ITEM | COUNT(*)
:--- | -------:
a    |        2
b    |        2
c    |        1
d    |        1

db<>fiddle here