SQLTeam.com | Weblogs | Forums

Find records in one table containing words stored in another table

Hi all!
I have 2 tables. First one contains user input, just some sentences. Second table contains blocked words. Now I need a script which selects all records from table 1 which contains at least 1 record from table 2. I've tried with LIKE or IN but was not successfull. Any ideas?

Here is an example:

CREATE TABLE #Messages (UserInput VARCHAR(500))
CREATE TABLE #BlockedWords (BlockedWord VARCHAR(50))

INSERT INTO #Messages(UserInput)
VALUES ('This is a test'),('This is another test'),('This is the 3rd test')

INSERT INTO #BlockedWords (BlockedWord)
VALUES ('something'),('word'),('another')

SELECT * FROM #Messages
---> WHERE [UserInput] contains any word from #BlockedWords <----

DROP TABLE #Messages
DROP TABLE #BlockedWords

The above example should return record "This is another Test" because it contains "another" from BlockedWords.
As you see, I need some help with WHERE [UserInput] contains any word from #BlockedWords. Is it possible to do something like this?

hi

i tried to do this
hope it helps :slight_smile: :slight_smile:

please click arrow to the left for "drop create data "
DROP TABLE #Messages
DROP TABLE #BlockedWords
go 

CREATE TABLE #Messages (UserInput VARCHAR(500))
CREATE TABLE #BlockedWords (BlockedWord VARCHAR(50))
go 

INSERT INTO #Messages(UserInput)
VALUES ('This is a test'),('This is another test'),('This is the 3rd test')

INSERT INTO #BlockedWords (BlockedWord)
VALUES ('something'),('word'),('another')
go 

SELECT * FROM #Messages
SELECT * FROM #BlockedWords
please click arrow to the left for "SQL "
SELECT * FROM #BlockedWords D 
CROSS APPLY 
   ( 
   SELECT * FROM #Messages E 
   WHERE E.UserInput like '%'+ D.BlockedWord +'%' 
   ) A 
GO

image

1 Like

It's more efficient to use EXISTS to avoid repeating rows in the output if that row has more than one word that matches. The performance on this could be poor. If this is something that runs a lot and/or the Messages tables is large, you should store the words in Messages separately and/or build an index/3ngram of the words in that table for better efficiency.

SELECT * 
FROM #Messages M
WHERE EXISTS (
    SELECT 1
    FROM #BlockedWords BW
    WHERE M.UserInput LIKE '%' + BW.BlockedWord + '%'
    )
2 Likes

Thanks Scott
:slightly_smiling_face::wink::wink::stuck_out_tongue_closed_eyes::+1::+1:

I am noticing this repeatedly a lot
Using exists for performance...sake

Need to note and implement next time

Many thanks to both of you, @harishgg1 and @ScottPletcher !! Works great!!