SQL SERVER - How To Count the Comma Separated Values

In Order to Count the number of individual values in a comma separated string we can use the Following simple method.

Let us see this through an example.

Let us create a new table Student_attendance that maintains the student id and their attendance details in a particular week as shown in the below format:

Create Table Student_attendance(StudId Int, DaysPresent Varchar(20))

Insert Into Student_attendance
Select   1,   '1,2,3,4'
Union All
Select   2,   '2,3,4'
Union All
Select   3,   '1,3,4,5,6'
Union All
Select   4,   '1,2,3,5,6,7'
Union All
Select   5,    '1,2,6'

Now, Let us see the Output of the Select Statement.

Select * From Student_attendance   

  

Now to count the number of days a student has attended to the class, we can count the number of occurrences of ',' and add 1 to the count.

This can be achieved by using the below query :

Select StudId,LEN(DaysPresent) - LEN(REPLACE(DaysPresent, ',', ''))+1 No_Of_Days_Present
From Student_attendance

This results :