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

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

Answer

Expounding on @Jaytiger’s answer with sample query and desired result using SPLIT().

Query:

WITH tmp AS
    (
       SELECT 'apple, orange, peach, peach, peach' as String UNION ALL
       SELECT 'potato, cucumber, pepper' 
    )
SELECT String, ARRAY_LENGTH(SPLIT(String)) AS Count FROM tmp;

Output: