Split Comma Seprate Value From Table In Sql Server

i have one SQL table in which many records, i want to know how many names are in it and how much time one name in it. Table NameMst Name john,smith,alax,rock smith,alax,sira john,

Solution 1:

You can extract the names using a recursive CTE and some string parsing. The rest is just aggregation:

with cte as (
      select (casewhen names like'%,%'thenleft(names, charindex(',', names) -1)
                   else names
              end) as name,
             (casewhen names like'%,%'thensubstring(names, charindex(',', names) +1, len(names))
              end) as names
      from names
      unionallselect (casewhen names like'%,%'thenleft(names, charindex(',', names) -1)
                   else names
              end) as name,
             (casewhen names like'%,%'thensubstring(names, charindex(',', names) +1, len(names))
              end)
      from cte
      where names isnotnull
     )
select name, count(*)
from cte
groupby name;

As you have probably figured out, storing comma delimited lists in SQL Server is a bad idea. You should have an association/junction table with one row per name (and other columns describing the list it is in).

Solution 2:

SELECT y.Name, count(*) Count
FROM
(VALUES
('john,smith,alax,rock'),
('smith,alax,sira'),
('john,rock'),
('rock,sira')) x(names)
CROSS APPLY
(
SELECT t.c.value('.', 'VARCHAR(2000)') Name
     FROM (
         SELECT x =CAST('<t>'+ 
               REPLACE(x.names, ',', '</t><t>') +'</t>'AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
     ) y
GROUPBY y.Name

Result:

Name   Count
alax   2
john   2
rock   3
sira   2
smith  2

Solution 3:

DECLARE@table1TABLE ( id VARCHAR(50) )
DECLARE@tableTABLE ( id1 INT,id VARCHAR(50) )
INSERTINTO@table (id1,id) values (1, 'JOHN,rom')
INSERTINTO@table (id1,id) values (2,'Micky,Raju')
INSERTINTO@table (id1,id) values (2,'Micky,Raju')
INSERTINTO@table (id1,id) values (2,'Micky,Raju')
DECLARE@MinINT,@MaxINT ,@str1VARCHAR(100),@str2VARCHAR(100)

DECLARE@xINT=0DECLARE@firstcommaINT=0DECLARE@nextcommaINT=0SELECT@x=   LEN(id) - LEN(REPLACE(id, ',', '')) +1from@table-- number of ids in id_list

WHILE @x>0BEGINSELECT@nextcomma=CASEWHEN CHARINDEX(',', id, @firstcomma+1) =0THEN LEN(id) +1ELSE CHARINDEX(',', id, @firstcomma-1)
                         ENDFROM@table--select @nextcommaINSERTINTO@table1SELECT  ( SUBSTRING(id, @firstcomma+1, (@nextcomma-@firstcomma) -1) ) FROM@tableSELECT@firstcomma= CHARINDEX(',', id, @firstcomma+1)FROM@tableSET@x=@x-1ENDSELECTDISTINCT id,COUNT(id)
FROM@table1GROUPBY id