x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 287

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

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
HyperBPP
• 2
1 Solution

Commented:
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 */)
``````
0

Commented:
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

Commented:
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
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.