Count the number of elements in a comma separated string in Oracle

Question

Say I have a table where Col2 is varchar

Col1 Col2
1    001,002
2    003,004,005

I need to count the number of elements in Col2,and return it, if I do:

select --do something here with column-- from table

it'll give me:

2
3 

Answer 1

So by counting the number of ,s you have in Col2 and adding 1 to it would give you your answer. Below I get the length of Col2. Then I replace the ,s with nothing and get that length. I take the first length and subtract the second length to get the total number of commas. Then simply add 1 to the result to get the total you are looking for:

SELECT (LENGTH(Col2) - LENGTH(REPLACE(Col2,",","")) + 1) AS MyCol2Count
FROM MyTable

Answer 2

If it's always formatted like that simply count the number of commas and then add 1:

select regexp_count(col, ',') + 1
  from table

Answer 3

Linger's answer is incorrect in the special case that Col2 is empty. Instead of yielding a count of 0 you'd get an incorrect count of 1. You can account for this special case with a CASE statement as follows:

SELECT CASE WHEN Col2='' THEN 0 ELSE LENGTH(Col2)-LENGTH(REPLACE(Col2,",",""))+1 END AS MyCol2Count
FROM MyTable

Edit: As William has pointed out the empty string WHEN test may be inaccurate if your table is setup to allow NULLs for the column in question. In such a case you'd need to replace the Col2='' test with Col2 IS NULL (at least in SQL Server).

Note: Apologies, I would have put this as a comment on Linger's answer but I'm not allowed to comment yet.