Find the number of words that contain a string from a SQL table

Question

I have been asked to search for a specific string and produce a list where a description field contains that string. Not a problem. In this case I used LIKE '%man%' and it gave a set of records containing 'man'. This also gives occurrences of 'manage', 'management', 'mankind', etc. I would like to find a recordset that contains the list of words found that include 'man' and the number of occurrences. Is this possible in SQL 2008? If so can you point me in the right direction?

Are you saying that you want a count for each distinct word in the list or just a count for each hit within a single description. — KHeaney, Dec 18 '14 at 17:21
Also, consider this: http://stackoverflow.com/questions/5444300/search-for-whole-word-match-with-sql-server-like-pattern — NoChance, Dec 18 '14 at 17:23
If the question involves "words", the answer typically involves [Full Text Search](http://msdn.microsoft.com/en-us/library/ms142571(v=sql.100).aspx) which has it's own word breaker and better search capabilities. Linguistic analysis ends up being a lot more complex than it first appears. — Bacon Bits, Dec 18 '14 at 17:26

score 0 · Answer 1 · answered Dec 18 '14 at 17:21

Your question is pretty vague and it is not at all clear what you want for output. See if something like this points you in the right direction.

declare @ValueToFind varchar(20) = 'man'

select * 
    , ABS((DATALENGTH(replace(SomeValue, @ValueToFind, ''))) - DATALENGTH(SomeValue)) / DATALENGTH(@ValueToFind)
from (Values('manage manager manman'), ('management'), ('mankind manfred'), ('none here')) v(SomeValue)

score 0 · Answer 2 · answered Dec 18 '14 at 17:43

How About:

CREATE TABLE #temp
(
[Description] varchar(250)
)

INSERT INTO #temp
VALUES
    ('a small step for mankind'),
    ('my manager says'),
    ('you might manage to purchase this'),
    ('try to manage this'),
    ('he was a small man'),
    ('no woman no cry'),
    ('no words can describe')



;
WITH CTE
As
(
    SELECT *, CHARINDEX('man',[Description] ) startPos, CHARINDEX(' ', [Description], CHARINDEX('man',[Description] )) endPos
    FROM #temp
    WHERE [Description] LIKE '%man%'
),
WordCTE
AS
(
    SELECT [Description], 
            CASE 
                WHEN endPos = 0 THEN SUBSTRING([Description], startpos, 100) 
                ELSE SUBSTRING([Description], startpos, endPos - startPos) 
            END AS [Word]
    FROM CTE
)
SELECT [Word], COUNT(*)
FROM WordCTE
GROUP BY [Word]

score 0 · Answer 3 · answered Dec 18 '14 at 17:56

I think you wanted something like this.

create table Words(text varchar(50))

insert into Words Values('man')
insert into Words Values('management')
insert into Words Values('man')
insert into Words Values('management')
insert into Words Values('mankind')
insert into Words Values('manpower')

select temptable.text, count(temptable.text) as 'count'
from
(select text 
from Words
where text like '%man%') as temptable
group by temptable.text

http://www.sqlfiddle.com/#!3/a6cdc/1

Find the number of words that contain a string from a SQL table

3 Answers3