How do I count the different strings in a list found found within each cell?-CodePudding

String
apple, orange, peach, peach, peach
potato, cucumber, pepper

I have a column such as the one above and I'm trying to write a query that creates a column showing the count of each item in the list. So the final table for the example above should look like this.

String	Count
apple, orange, peach, peach, peach	5
potato, cucumber, pepper	3

CodePudding user response：

Expounding on @Jaytiger's answer with sample query and desired result using

CodePudding user response：

You can use SPLIT_STRING() for that purpose:

SELECT a.String, COUNT(b.value) AS Count
FROM Table1 a
CROSS APPLY STRING_SPLIT(a.String, ',') AS b
GROUP BY a.String

Noticed that the question is related to Google BigQuery. My answer is applicable to MS SQL Server

CodePudding user response：

Non-orthodox approach - at least for fun and if you are in the learning mode :o)

select *, length(regexp_replace(String, r'[^,]', ''))   1 as Count
from your_table

with output