Count comma separated values of all columns in Oracle SQL

Question

I have already went through a number of questions and I couldn't find what I am exactly looking for. Suppose I have a table as follows :

Col1        Col2        Col3
1,2,3       2,3,4,5,1   5,6

I need to get a result as follows using a select statement:

Col1        Col2        Col3
1,2,3       2,3,4,5,1   5,6
3           5           2

Note the added third column is the count of comma separated values. Finding the count for a single column is simple, but this seems difficult if not impossible. Thanks in advance.

Don't store data like comma separated values in a column, it will only cause problems... BTW, you've added a row, not a column. — jarlh, Jan 12 '15 at 12:26
`mysql` or `oracle` those are two very different DBMS. @jarlh: I assume the answer to your comment is "I didn't design it this way. I just have to deal with it". Because no one who creates such a design actually has to use it. — , Jan 12 '15 at 12:27
Yes @jarlh, How else can I fetch the count values for multiple columns. I have found in answers [here](http://stackoverflow.com/questions/27901978/count-comma-separated-values-of-all-columns-in-oracle-sql) how to do it for one column. But I have multiple columns and I don't want to write multiple queries. — RJ Doe, Jan 12 '15 at 12:28
This design appears to violate the first normal form. If so, you may want to structure your tables differently. — nwk, Jan 12 '15 at 12:29
look this may be help you http://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch — Durgpal Singh, Jan 12 '15 at 12:31
What you are showing is not clear to me. It seems to be a table with three columns and only one record. Yes? The columns are varchar columns and the first cell contains the string '1,2,3'. Is this correct? And you want to select the one record plus a second giving the counts of numbers in the strings? (The "added third column" being the added second row actually.) — Thorsten Kettner, Jan 12 '15 at 12:45

score 3 · Accepted Answer · edited Jan 12 '15 at 12:48

3

select
col1,
regexp_count(col1, ',') + 1 as col1count,
col2,
regexp_count(col2, ',') + 1 as col2count,
col3,
regexp_count(col3, ',') + 1 as col3count
from t

FIDDLE

FIDDLE2

edited Jan 12 '15 at 12:48

RJ Doe

49
1
4

answered Jan 12 '15 at 12:36

Mihai

26,325
7
66
81

This only gets the count and I need the data as well. But your answer gave me a diferent idea on how to do it. Thanks for that. – RJ Doe Jan 12 '15 at 12:43
@RJDoe There is a second fiddle in there which does just that. – Mihai Jan 12 '15 at 12:44
1

Thanks for the help man, already voted your answer as the right answer. Thansk again. – RJ Doe Jan 12 '15 at 12:48
@Rjdoe, but you said in question that you don't want to do for each column, hence I gave the PL/SQL solution too. – Lalit Kumar B Jan 12 '15 at 14:28

score 2 · Answer 2 · edited May 23 '17 at 12:05

Per Count the number of elements in a comma separated string in Oracle an easy way to do this is to count the number of commas and then add 1

You just need the result unioned onto your original data. So, do that:

SQL> with the_data (col1, col2, col3) as (
  2  select '1,2,3', '2,3,4,5,1', '5,6' from dual
  3         )
  4  select a.*
  5    from the_data a
  6   union all
  7  select to_char(regexp_count(col1, ',') + 1)
  8       , to_char(regexp_count(col2, ',') + 1)
  9       , to_char(regexp_count(col3, ',') + 1)
 10    from the_data;

COL1  COL2      COL
----- --------- ---
1,2,3 2,3,4,5,1 5,6
3     5         2

You need to convert the result to a character because you're unioning a character to a number, which Oracle will complain about.

It's worth noting that storing data in this manner violates the first normal form. This makes it far more difficult to manipulate and almost impossible to constrain to be correct. It's worth considering normalising your data model to make this, and other queries, simpler.

Ah! @Ben, this time I was quick...lol ;-) I left the `union` bit for OP to figure out. — Lalit Kumar B, Jan 12 '15 at 12:56

score 1 · Answer 3 · answered Jan 12 '15 at 12:38

Finding the count for a single column is simple, but this seems difficult if not impossible.

So you don't to look for each column manually? You want it dynamically.

The design is actually flawed since it violates normalization. But if you are willing to stay with it, then you could do it in PL/SQL using REGEXP_COUNT.

Something like,

SQL> CREATE TABLE t AS
  2  SELECT '1,2,3' Col1,
  3    '2,3,4,5,1' Col2,
  4    '5,6' Col3
  5  FROM dual;

Table created.

SQL>
SQL> DECLARE
  2    cnt NUMBER;
  3  BEGIN
  4    FOR i IN
  5    (SELECT column_name FROM user_tab_columns WHERE table_name='T'
  6    )
  7    LOOP
  8      EXECUTE IMMEDIATE 'select regexp_count('||i.column_name||', '','') + 1 from t' INTO cnt;
  9      dbms_output.put_line(i.column_name||' has cnt ='||cnt);
 10    END LOOP;
 11  END;
 12  /
COL3 has cnt =2
COL2 has cnt =5
COL1 has cnt =3

PL/SQL procedure successfully completed.

SQL>

Probably, there will be an XML solution in SQL itself, without using PL/SQL.

In SQL -

SQL> WITH DATA AS
  2    ( SELECT '1,2,3' Col1, '2,3,4,5,1' Col2, '5,6' Col3 FROM dual
  3    )
  4  SELECT regexp_count(col1, ',') + 1 cnt1,
  5    regexp_count(col2, ',')      + 1 cnt2,
  6    regexp_count(col3, ',')      + 1 cnt3
  7  FROM t;

      CNT1       CNT2       CNT3
---------- ---------- ----------
         3          5          2

SQL>

Count comma separated values of all columns in Oracle SQL

3 Answers3