CODEWITHSUNDEEP
sqlsql-server
Shawn
Shawn

Reputation: 5260

t-sql: Count the number of elements

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.

Upvotes: 1

Views: 9314

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can calc difference in length:

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

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 3

Related Questions