Is there a LIKE operator for a dynamic list or set of multiple strings on MS SQL Server?

Question

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.

score 3 · Accepted Answer · answered Sep 24 '19 at 08:32

3

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, '%')

answered Sep 24 '19 at 08:32

GMB

216,147
25
84
135

Thanks @GMB, somehow `CONCAT` or `'%' + t1.substring + '%'` did not work and I instead get duplicates of rows which fulfill the criteria. However `CHARINDEX` works fine. – jackson95 Sep 26 '19 at 02:06

score 1 · Answer 2 · answered Sep 24 '19 at 08:42

1

You can try using this query:

SELECT t2.*, t1.substring
FROM table2 t2
INNER JOIN table1 t1 ON t2.comment LIKE '%' + t1.substring + '%'

answered Sep 24 '19 at 08:42

Pawan Gupta

177
8

Is there a LIKE operator for a dynamic list or set of multiple strings on MS SQL Server?

2 Answers2