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?
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
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 + '%'
)
