sql server comma separated values count

Following steps will explains how to write query for count comma separated values

1)Create table using following Query

/****** Object: Table [dbo].[GroupsDetails] Script Date: 05/31/2011 21:38:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GroupsDetails](
[GroupId] [int] NULL,
[GroupMembers] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2)Insert table data using following query

INSERT INTO [GroupsDetails]([GroupId],[GroupMembers])VALUES(1,'manikandan,raja,kumar,selvem')
INSERT INTO [GroupsDetails]([GroupId],[GroupMembers])VALUES(2,'balaji,sankar')
INSERT INTO [GroupsDetails]([GroupId],[GroupMembers])VALUES(3,'maha,raja')
INSERT INTO [GroupsDetails]([GroupId],[GroupMembers])VALUES(4,'maha,mala')

3)Following query will give comma separated total count in every rows

SELECT GroupId,LEN(GroupMembers) - LEN(REPLACE(GroupMembers, ',', ''))+1 as TotalGroupMembes
FROM GroupsDetails

Out put



happy coding..