I am trying to update a table with ALL pattern matches in a string. I have three tables.

1 - 'Stack' Containing my Circumstance -- This is a long string free text field

2 - 'WordMatches' Contains a list of words of interest to me

3 - 'WordExclude' Contains the strings to exclude where a match occurs

I would like to PRINT each matched word into a new column and count the number of matched words. The nature and number of words is of importance to the work I am doing.

For example, Stack

Word Match

Word Exclude

I want an output that looks like

I feel like this is a simple task but I cannot figure out a way for the LIKE operation to keep searching the string for multiple hits. All I have manage to achieve is flag a row which has at least one hit on the WordMatch table.

Any help would be much appreciated.

Thanks, Jack

CodePudding user response：

Here is one way to do it, note that the word ice matches in the word Police so I have a count of 1 there.
If you don't want that you could add a space before the values in table WordMatch

declare @Circumstance table (value varchar(100))
declare @WordMatch table (id int, wordmatch varchar(50))

insert into @Circumstance values ('The police where called'), ('Ice froze the door shut and wind blew the roof off')
insert into @WordMatch values (1, 'Blew'), (2, 'Froze'), (3, 'Ice'), (4, 'Wind')


select c.value,
       ( select string_agg(wm.wordmatch, ' ')
         from   @WordMatch wm
         where  CHARINDEX(wm.wordmatch, c.value) > 0
       ) words,
       sum(case when CHARINDEX(wm.wordmatch, c.value) > 0 then 1 else 0 end)
from   @Circumstance c
  cross join @WordMatch wm
group by c.value

the result is

Here is a