SQL Count all word occurrences from a table

Question

I'm attempting to create a word cloud from a customer survey table. I wanted to count all word occurrences from a specific column in a table. This columnn holds all customer survey comments. I was attempting to follow the instructions below but was unable to figure out how to code and count for all word occurrences for all customer comments.

http://sqljason.com/2012/03/making-tag-cloud-with-ssrs-rich-text.html

Apologies somewhat new to SQL.

What version of SQL Server are you working on? – M.Ali May 22 '18 at 22:05 — M.Ali, May 22 '18 at 22:05

score 1 · Answer 1 · answered May 22 '18 at 22:57

I tested this on MS SQL 2012 and 2014.

    --Create table
    DECLARE @t TABLE (RowNum int null, comments varchar(max))

    --Build table
    INSERT INTO @t
    (RowNum, comments)
    SELECT ROW_NUMBER() OVER(ORDER BY example.comment DESC) AS RowNum,
           REPLACE(REPLACE(example.comment, '!', ''), ',', '')  FROM
    (
    SELECT 'This website is awesome' AS comment UNION
    SELECT 'I like your website, however it could be better' UNION
    SELECT 'The menu button at the top is really nice!'
    )example

    --Show table
    SELECT * FROM @t

    --Setup vars
    DECLARE @i int = 1
    DECLARE @Count int = (SELECT COUNT(t.RowNum) FROM @t t)
    DECLARE @delimiter varchar(1) = ' '
    DECLARE @output TABLE(splitdata NVARCHAR(MAX))

    --Iterate through a table and build output table
    WHILE @i <= @Count
    BEGIN
        --Do something on one row at a time ie: WHERE(RowNum = @i)
        DECLARE @string varchar(max) = (SELECT t.comments FROM @t t WHERE(t.RowNum = @i))

        DECLARE @start int
        DECLARE @end int 
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
          SET @end = LEN(@string) + 1
          INSERT INTO @output (splitdata)  
          VALUES(SUBSTRING(@string, @start, @end - @start)) 
          SET @start = @end + 1 
          SET @end = CHARINDEX(@delimiter, @string, @start)
        END 
        SET @i = @i + 1 --Iterate i
    END

    --Show output table
    SELECT * FROM @output

    --Summarize words
    SELECT o.splitdata, COUNT(*) AS Cnt FROM @output o
    GROUP BY o.splitdata
    ORDER BY Cnt DESC

score 0 · Answer 2 · answered May 22 '18 at 22:06

Easiest way is : where Countwords = Column to check and WET is the word you are counting for.

Select SUM(CTS) 'TotalOccurancesOfWord'
from
( 
SELECT LEN(countwords) - LEN(REPLACE(countwords, 'wet', '')) CTS
from #temp --table you are using
) a

also see : How to count instances of character in SQL Column

score 0 · Answer 3 · answered May 22 '18 at 22:13

If you are working on SQL Server 2016 or later version, the following can do the trick for you:

SELECT 
        REPLACE(REPLACE(s.value, ',', ''), ' ', '') [Word]
      , COUNT(*) Occurrence

FROM  TableName t
           CROSS APPLY STRING_SPLIT( [Comments] , ' ') s

GROUP BY REPLACE(REPLACE(s.value, ',', ''), ' ', '')

If you are working on an older version, older than 2016 SQL Server then you will need to use user defined [Split String] function, there are a lot of example online, just google it. But you can use the above query with that older version of [Split String] user defined function.

Also for the SSRS report, I would populate a table with this data as frequently as I need to and point the report to that table, but would not recommend executing this command every time the report is executed.

SQL Count all word occurrences from a table

3 Answers3