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

Answer 2

Split titles to rows and count them.

SQL> with test (id, title) as
  2    (select 1, 'a,b,c' from dual union all
  3     select 2, 'a,d'   from dual union all
  4     select 3, 'b'     from dual
  5    ),
  6  temp as
  7    (select regexp_substr(title, '[^,]', 1, column_value) val
  8     from test cross join table(cast(multiset(select level from dual
  9                                              connect by level <= regexp_count(title, ',') + 1
 10                                             ) as sys.odcinumberlist))
 11    )
 12  select val as title,
 13         count(*)
 14  From temp
 15  group by val
 16  order by val;

TITLE                  COUNT(*)
-------------------- ----------
a                             2
b                             2
c                             1
d                             1

SQL>

---

If titles aren't that simple, then modify REGEXP_SUBSTR (add + sign) in line #7, e.g.

SQL> with test (id, title) as
  2    (select 1, 'Robin Hood,Avatar,Star Wars Episode III' from dual union all
  3     select 2, 'Mickey Mouse,Avatar'   from dual union all
  4     select 3, 'The Godfather'     from dual
  5    ),
  6  temp as
  7    (select regexp_substr(title, '[^,]+', 1, column_value) val
  8     from test cross join table(cast(multiset(select level from dual
  9                                              connect by level <= regexp_count(title, ',') + 1
 10                                             ) as sys.odcinumberlist))
 11    )
 12  select val as title,
 13         count(*)
 14  From temp
 15  group by val
 16  order by val;

TITLE                            COUNT(*)
------------------------------ ----------
Avatar                                  2
Mickey Mouse                            1
Robin Hood                              1
Star Wars Episode III                   1
The Godfather                           1

SQL>