Question: | 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()](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split).

Query:

```sql
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:

[![enter image description here](https://i.stack.imgur.com/UMKBI.png)](https://i.stack.imgur.com/UMKBI.png)
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

[![enter image description here](https://i.stack.imgur.com/eKcJX.png)](https://i.stack.imgur.com/eKcJX.png)