October 21, 2015 at 8:29 pm
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
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;
-- Itzik Ben-Gan 2001
October 21, 2015 at 9:28 pm
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
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 stringThen 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.
-- Itzik Ben-Gan 2001
October 22, 2015 at 4:33 am
Just finished reading http://www.sqlservercentral.com/articles/Tally+Table/72993/
Impresive
October 22, 2015 at 7:55 am
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.
-- 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