Suppose I have a result set of only one column containing substrings:
Table1:
substrings ----------- substringa substringb substringc substringd substringe etc. -----------
And I have a second result set from another query:
Table2:
id | comment ------------------------------------------------------------------------------- 0001 | A text containing substringa 0002 | A text containing substringd 0003 | A text containing none of the substrings from Table1 0004 | Another text containing substringa 0005 | A text containing substringb ... | etc. -------------------------------------------------------------------------------
I want to return a third table, Table3, containing the rows in Table2 where the comment contains any of the substrings existing in Table1, including another column with the existing substrings itself, i.e.:
Table3:
id | comment | substrings ------------------------------------------------------------------------------- 0001 | A text containing substringa | substringa 0002 | A text containing substringd | substringd 0004 | Another text containing substringa | substringa 0005 | A text containing substringb | substringb ... | etc. | ... -------------------------------------------------------------------------------
It can be assumed that all the comments in Table2 contains exactly zero or one of the substrings in Table1.
I tried looking for a solution using a combination of charindex, substring, exists or like operators but failed to come up with any solution, and MS SQL Server has no suitable regexp operator that I know of. Is there something similar to a like operator to check for multiple strings on MS SQL Server, or are there better methods to do this? The size of substrings in Table1 is in the order 10^2-10^3 and is dynamically changing, so I cannot hardcode as described in this post.
Advertisement
Answer
One solution would be to use use CHARINDEX() to check if a string contains another string:
SELECT t2.*, t1.substring FROM table2 t2 INNER JOIN table1 t1 ON CHARINDEX(t1.substring, t2.comment) > 0
If you want to see also comments that do not have a matched substring, you can use LEFT JOIN instead of INNER JOIN.
LIKE would get the job done as well:
SELECT t2.*, t1.substring
FROM table2 t2
INNER JOIN table1 t1 ON t2.comment LIKE CONCAT('%', t1.substring, '%')