Search code examples
stringsql-server-2008count

SQL Comma separated string total count

Schema : 

SubscriberId NewsletterIdCsv 
------------ --------------- 
1     48,51,94     
2     43,22
3     33,11
4     90,61

I Need to get the count for each rows NewsletterIdCsv and then add all of them up to get a total count of all rows, for the basic row count I am doing the following:

SELECT newsletteridcsv, len(newsletteridcsv) - len(replace(newsletteridcsv, ',', '')) +1 IndividualCount
FROM DBTABLE

This Gives me the Result :

NewsletterIdCsv IndividualCount
------------ --------------- 
48,51,94     3
43,22        2
33,11        2
90,61        2

How do I get the total count (In this example 9)?

Note : This table has 5 Million records and I do not think using a temp table to Insert count and then finally going through the temp table rows to accumulate the count is an optimized way? Also I am totally against using cursors for efficiency issues !

What's the best way to get the total count ?

Solution

  • You can use SUM to add them together:

    SELECT SUM(len(newsletteridcsv) - len(replace(newsletteridcsv, ',', '')) +1) 
    AS TotalCount
FROM DBTABLE

    Since you're just asking for the total count, you don't even need to GROUP BY anything.