Question: I have a table like this in SQL Server 2008:

```
create table test (id int, array varchar(max))
insert into test values (1,',a,b,c,d')
insert into test values (2,',a,b,c,d,e')
insert into test values (3,',a,b,c')

```

I want to count the number of elements of the array column with the result being the following:

```
id   count
---  -----
1    4 
2    5
3    3

```

Any ideas how to achieve this in a SELECT statement? I understand that making a function that processes the count could help, but just want to know if it can be achieved without a user defined function.

Answer: 
By far the best option would be to stop storing array in your database. This violates 1NF and it a poor design decision. 

You can however get the results you are looking for with a simple replace.

```
select ID
    , LEN(array) - LEN(replace(array, ',', ''))
from test

```
You can calc difference in length:

```
select id, len(array) - len(replace(array, ',', ''))
from test

```