Post reply

How to count the instances of each word throughout comments field across the table?

  • October 21, 2015 at 8:29 pm

    #305793

    I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:

    Row# Comments

    1 I like working on SQL

    2 I enjoy sleeping and like watching TV

    So the output should be :

    Word Count

    I 2

    like 2

    working 1

    on 1

    SQL 1

    .......

    Could someone please suggest if there is a way to get this count using MS SQL?

    Thanks!

  • October 21, 2015 at 9:05 pm

    #1834805

    Using the splitter in my signature line you could do this:

    -- let's create some easily consumable DDL

    DECLARE @YourTable TABLE (RowNumber int primary key, Comments varchar(1000) NOT NULL);

    -- Insert sample values

    INSERT @YourTable

    VALUES (1, 'I like working on SQL'),(2, 'I enjoy sleeping and like watching TV');

    -- The solution using delimitedsplit8K

    SELECT Item, COUNT(item)

    FROM @YourTable

    CROSS APPLY dbo.DelimitedSplit8K(Comments,' ')

    GROUP BY Item;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • October 21, 2015 at 9:28 pm

    #1834806

    Sounds like you need to write a SQL script that will have a cursor that queries the table that has the column holding the string

    Then you will have to use a While loop to substring each word into a table (eg WordTbl) that will have the word the record key ID of the word belongs to.

    Then you can run a select from the WordTbl with a appropriate aggregate (count or sum)

  • October 21, 2015 at 9:57 pm

    #1834808

    ignacio.jose (10/21/2015)

    Sounds like you need to write a SQL script that will have a cursor that queries the table that has the column holding the string

    Then you will have to use a While loop to substring each word into a table (eg WordTbl) that will have the word the record key ID of the word belongs to.

    Then you can run a select from the WordTbl with a appropriate aggregate (count or sum)

    What I posted will do the job much, much faster than the While Loop/cursor into a holding table solution you are suggesting.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • October 22, 2015 at 4:33 am

    #1834846

    Just finished reading http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Impresive

  • October 22, 2015 at 7:55 am

    #1834903

    ignacio.jose (10/22/2015)

    Just finished reading http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Impresive

    That article certainly helped me a great deal.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply