This code snippet will allow the user to find out the total number of comma separated phrases being stored as a single string. Anybody trying to get the total number of phrases present in that column will face difficulty, as a group of phrases in a row would appear as a single phrase. This code snip offers a solution to similar kind of problems. Also, the viewer can find the number of entities present in a single row by using group by clause for each row separately.

The above stated problem can be represented as the figure below. Let us say we have a table consisting of two fields as NAME and ID and has been maintained as follows.

Figure 1

In order to find out how many names are there in the NAME field we cannot put sum function across it. This is because by putting sum across the NAME we will find only 5 instead of 17.

Listing 1

select sum(length(name) - length(translate(name,' ,',' '))+1) NAMES 
from multiple_id;

Figure 2

Here I have tried to find out the number of commas in a row. Notice that "n" numbers of names have been stored as a string being separated by n-1 numbers of commas in between. So each string is having n (n-1 +1) names. Any string having at least one name without any comma cannot even skip the logic.

Listing 2

select id,sum(length(name)-length(translate(name,' ,',' '))+1)NAME 
from multiple_id group by id;

Figure3

We dealt with comma-delimited entities stored as a string. The translate function can also be used for other delimited entities stored as a string. With proper modification we can make the query follow our requirement.