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)
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