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?

Answer 1

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

Answer 2

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)

Answer 3

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]