CODEWITHSUNDEEP
sqlsql-serverjoin
TheExecutorofQueries
TheExecutorofQueries

Reputation: 5

Finding all values from one column within another column in a different table

I am trying to iterate through all values in a column Keyword in a table Keyword_Table and search for these values within a column Block_of_Text in another table Raw_Text. The Keywords from Keyword_Table are embedded within a large amount of text in Raw_Text.Block_of_Text. Another important note is that the Keyword_Table is about 4000 records large and the table Raw_Text is about 4,000,000 records. In the end, I would like to have output that would contain a column with the value from Keyword_Table.Keyword and the full Raw_Text.Block_of_Text value containing the corresponding value from Keyword_Table.Keyword. There are no values in common between the two tables so I can't use joins I don't think.

Currently I am trying to use this code but given the scale of the tables it is extremely inefficient. I added a column called Corresponding_Keyword to Raw_Text

UPDATE [Raw_Text]
SET Corresponding_Keyword =
(
CASE
WHEN Block_of_Text LIKE CONCAT('%','XXXX','%')  Then 'XXXX'
WHEN Block_of_Text LIKE CONCAT('%','YYYY','%')  Then 'YYYY'
WHEN Block_of_Text LIKE CONCAT('%','ZZZZ','%')  Then 'ZZZZ'
END
)

But in this situation it is not 3 Cases, but 4000. So obviously this solution is unreasonable. Does anyone have any insight on how I may best handle this situation. The lack of a field to join seems like a problem for me but is this just intrinsically difficult given the nature of the task with large tables? Thanks.

Upvotes: 0

Views: 1138

Answers (1)

Fuzzy
Fuzzy

Reputation: 496

Doing a JOIN like this should work, I am not sure about the performance though:

SELECT kw.Keyword, t.Block_of_Text
FROM Keyword_Table kw
JOIN Raw_Text t ON t.Block_of_Text LIKE '%' + kw.Keyword + '%'

Upvotes: 1

Related Questions