Solved

MS SQL for count of words in a series of sentences.

Posted on 2013-11-12
3
259 Views
Last Modified: 2013-11-13
Need a query that counts number of occurances of the word similar to count(field).  I have a db where each record has a sentence.  Count(sententFiled) returns the number of sentences with a word.  This is lower than the actual number of words because some words occur more than once in a sentence.

Thanks!
0
Comment
Question by:HyperBPP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39642870
try this function

create function [dbo].[fnParseWords](@str varchar(max), @delimiter varchar(30)='%[^a-zA-Z0-9\_]%')
returns @result table(word varchar(max))
begin
    if left(@delimiter,1)<>'%' set @delimiter='%'+@delimiter;
    if right(@delimiter,1)<>'%' set @delimiter+='%';
    set @str=rtrim(@str);
    declare @pi int=PATINDEX(@delimiter,@str);

    while @pi>0 begin
        insert into @result select LEFT(@str,@pi-1) where @pi>1;
        set @str=RIGHT(@str,len(@str)-@pi);
        set @pi=PATINDEX(@delimiter,@str);
    end

    insert into @result select @str where LEN(@str)>0;
    return;
end
go

select COUNT(*)
from webqueries q
cross apply dbo.fnParseWords(cast(q.qQuestion as varchar(max)),default) pw
where pw.word not in ('and','is','a','the'/* plus whatever else you need to exclude */)

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39643700
this will count the number of times a string is found in a larger string:

( len(sentence) - len(replace(sentence,'word','') ) / len('word')

{+edit} and just sum that if aggregating
sum( ( len(sentence) - len(replace(sentence,'word','') ) / len('word') )
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39643706
example, sample data: 5 sentences containing 'amet', 10 occurences of 'amet':
    CREATE TABLE SentenceTable
    	([SentenceField] varchar(800)) 
    ;
    	
    INSERT INTO SentenceTable
    	([SentenceField])
    VALUES
    	('Lorem ipsum dolor sit amet, consectetur adipiscing elit amet.'),
    	('Fusce euismod justo id rhoncus lobortis.'),
    	('Nunc rhoncus amet risus vitae metus amet laoreet placerat amet.'),
    	('Nam vel nunc dapibus, suscipit eros ut, imperdiet erat.'),
    	('Proin ut enim fringilla, iaculis erat nec, mattis risus.'),
    	('Donec ac leo egestas, amet euismod velit id, blandit nunc.'),
    	('Vivamus vestibulum est non purus faucibus mattis.'),
    	('Vivamus in tortor ultrices, cursus massa eget, ornare justo.'),
    	('Etiam lobortis nunc nec commodo pretium.'),
    	('Nam in neque et mauris lobortis euismod.'),
    	('Suspendisse amet eget neque malesuada, amet cursus ligula et, sodales odio amet.'),
    	('Ut eget est facilisis, aliquet leo ac, posuere enim.'),
    	('Praesent consequat augue sed erat fermentum, sit amet fringilla turpis malesuada.'),
    	('Curabitur eget eros eget massa tempor interdum.')
    ;

**Query 1**:

    declare @word as varchar(100)
    set @word = 'amet'
    
    SELECT
      count(*)
    , sum( (len(SentenceField) - len(replace(SentenceField,@word,''))) / len(@word) )
    FROM SentenceTable
    WHERE SentenceField LIKE '%' + @word + '%'
    

**[Results][2]**:
    
    | COLUMN_0 | COLUMN_1 |
    |----------|----------|
    |        5 |       10 |


**Query 2**:

    declare @word as varchar(100)
    set @word = 'amet'
    
    SELECT
      SentenceField
    , (len(SentenceField) - len(replace(SentenceField,@word,''))) / len(@word)
    FROM SentenceTable
    WHERE SentenceField LIKE '%' + @word + '%'
    

**[Results][3]**:
    
    |                                                                     SENTENCEFIELD | COLUMN_1 |
    |-----------------------------------------------------------------------------------|----------|
    |                     Lorem ipsum dolor sit amet, consectetur adipiscing elit amet. |        2 |
    |                   Nunc rhoncus amet risus vitae metus amet laoreet placerat amet. |        3 |
    |                        Donec ac leo egestas, amet euismod velit id, blandit nunc. |        1 |
    |  Suspendisse amet eget neque malesuada, amet cursus ligula et, sodales odio amet. |        3 |
    | Praesent consequat augue sed erat fermentum, sit amet fringilla turpis malesuada. |        1 |



  [1]: http://sqlfiddle.com/#!3/8dc0e/1

Open in new window

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question