I have a column in a table with words separated by comma. I need to count each occurence of each word
My column looks like : ('a, b, c'), ('a, b, d'), ('b, c, d'), ('a'), ('a, c'); (fiddle at the bottom)

Here is what I get :

MyCol        Count
-----------------
a           1
a, b, c     3
a, b, d     3
a, c        2
b, c, d     3

But here is what I expect

MyCol    Count
-------------
a        4
b        3
c        3
d        2

Here is what I've done so far :

select MyCol, COUNT(*)
from Test
cross apply string_split(MyCol, ',')
group by MyCol

Fiddle : http://sqlfiddle.com/#!18/4e52e/3

Please note the words are separated by a comma AND a space

CodePudding user response：

You are using the wrong column. Simply use the [value] column (returned from the STRING_SPLIT() call) and remove the space characters (using TRIM() for SQL Server 2017 or LTRIM() and RTRIM() for earlier versions):

SELECT TRIM(s.[value]) AS [value], COUNT(*) AS [count]
FROM Test t
CROSS APPLY STRING_SPLIT(t.MyCol, ',') s
GROUP BY TRIM(s.[value])
ORDER BY TRIM(s.[value])

CodePudding user response：

select value,count(*)cntt
from Test
cross apply string_split(MyCol,',')
group by value
order by value;

CodePudding user response：

remove white space by using REPLACE and then use Subquery

select MyCol,count(MyCol) as Count from(
select  
REPLACE (value, ' ', '' ) as MyCol
--TRIM(value) as MyCol -- both TRIM and REPLACE are equivalent 
----comment one of them
from test
cross apply string_split(MyCol, ',')) b
group by MyCol

fiddle

CodePudding user response：

Simply :

select MyCol, 1   LEN(MyCol) - LEN(REPLACE(MyCol, ',', '')) AS NUM
from Test