By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- The exact banned word matching code:
DECLARE @tableFinal TABLE (
t0_BlogCommentId int,
t0_Word VARCHAR(50),
t0_Text1 varchar(10),
t0_Text2 varchar(10),
t0_Text3 varchar(10),
t0_CntOfBannedWords int)
DECLARE @table1 TABLE (
t_BlogCommentId int,
t_Word VARCHAR(50),
t_Text1 varchar(10),
t_Text2 varchar(10),
t_Text3 varchar(10));
DECLARE @BlogComment TABLE (
BlogCommentId INT IDENTITY PRIMARY KEY,
BlogCommentContent VARCHAR(MAX),
Text1 varchar(10),
Text2 varchar(10),
Text3 varchar(10));
INSERT INTO @BlogComment
(BlogCommentContent,
Text1,
Text2,
Text3)
VALUES
('There are many of us.', 'a', 'b', 'c'), -- word in the BlogCommentContent is NOT banned - it's a variant of it. So NOT selected.
('The man.', 'e', 'f', 'g'), -- 1 word in the BlogCommentContent - word is banned. MUST be selected and counted as 0 non-unique banned word.
('So glad to hear about.', 'h', 'i', 'j'), -- 2 different words in the BlogCommentContent are banned. MUST be selected as 1 row and counted as 2 non-unique banned words.
('So glad to hear about. A regular guy.', 'k', 'l', 'm'), -- 2 different words in the BlogCommentContent are banned. MUST be selected as 1 row and counted as 2 non-unique banned words.
('though, though, though.', 'n', 'o', 'p'); -- 3 same words in the BlogCommentContent - word is banned. MUST be selected as 1 row and counted as 0 non-unique banned word.
SELECT 'Blog comment table'
| (No column name) |
|---|
| Blog comment table |
| BlogCommentId | BlogCommentContent | Text1 | Text2 | Text3 |
|---|---|---|---|---|
| 1 | There are many of us. | a | b | c |
| 2 | The man. | e | f | g |
| 3 | So glad to hear about. | h | i | j |
| 4 | So glad to hear about. A regular guy. | k | l | m |
| 5 | though, though, though. | n | o | p |
| (No column name) |
|---|
| Banned Words table |
| BannedWordsId | Word |
|---|---|
| 1 | though |
| 2 | man |
| 3 | about |
| 4 | hear |
| (No column name) |
|---|
| Results from the WITH - before collapsing |
| t_BlogCommentId | t_Word | t_Text1 | t_Text2 | t_Text3 |
|---|---|---|---|---|
| 2 | man | e | f | g |
| 3 | hear | h | i | j |
| 3 | about | h | i | j |
| 4 | hear | k | l | m |
| 4 | about | k | l | m |
| 5 | though | n | o | p |
| 5 | though | n | o | p |
| 5 | though | n | o | p |
| (No column name) |
|---|
| FINAL desired result |
| BlogCommentId | BannedWords | Text1 | Text2 | Text3 | CntOfBannedWordsInComment |
|---|---|---|---|---|---|
| 2 | man | e | f | g | 1 |
| 3 | hear,about | h | i | j | 2 |
| 4 | hear,about | k | l | m | 2 |
| 5 | though | n | o | p | 3 |