title: Counting comma separated values in TSQL
body: <p>SCHEMA / DATA for TABLE :</p>
  
  <pre><code>SubscriberId NewsletterIdCsv
  ------------ ---------------
  11           52,52,,52  
  </code></pre>
  
  <p>We have this denormalized data, where I need to count the number of comma separated values, for which I am doing this :</p>
  
  <pre><code>SELECT SUM(len(newsletteridcsv) - len(replace(rtrim(ltrim(newsletteridcsv)), ',','')) +1) as SubscribersSubscribedtoNewsletterCount
  FROM TABLE
  WHERE subscriberid = 11
  </code></pre>
  
  <p>Result :</p>
  
  <pre><code>SubscribersSubscribedtoNewsletterCount
  --------------------------------------
  4
  </code></pre>
  
  <p>The problem is some of our data has blanks / spaces in between the comma separated values, if I run the above query the expected result should be 3 (as one of the value is blank space), how do I check in my query to exclude the blank spaces?</p>
  
  <p>EDIT :</p>
  
  <p>DATA :</p>
  
  <pre><code>SubscriberId NewsletterIdCsv
  ------------ ---------------
  11           52,52,,52  
  12           22,23
  </code></pre>
  
  <p>I need to get an accumulative SUM instead of just each rows sum, so for this above data I need to have just a final count  i.e. 5 in this case, excluding the blank space.</p>