Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-12
3
Medium Priority
?
276 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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

721 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