Sql-server – Select a set of keywords from table1 and search for strings containing these keywords in table2

pattern matchingsql server

I have two tables: t1 and t2. There are three steps:

  1. In t1 select a set_of_strings with id = 1 ("1a" and"3c").
  2. From t1 select strings that correspond to OPTIONID ='A' and that contain substrings from the set_of_strings.

t1

ID  NAME   
1   "1a"   
2   "2b"   
1   "3c"

t2

OPTIONID    TXT   
  A     "7h 9t"   
  B     "1a 8n"   
  A     "2b 4r"    
  A     "3c 6u"

Queries

SELECT 
    NAME 
FROM t1 
WHERE ID = 1; -- 1a, 3c

SELECT
    TXT 
FROM t2 
WHERE OPTIONID = 'A' AND (TXT LIKE '%1a%' OR TXT LIKE '%3c%') --"3c 6u"

Question

How to automate the TXT LIKE '%1a%' OR TXT LIKE '%3c%' part?

LIKE'%1a%' is safe because of all entries have the same format, i.e. there is no 1a2 in TXT column.

Best Answer

Something like:

select t2.txt
from t1
join t2
    on t2.txt like '%' + t1.name + '%'
where t2.char = 'A'

Adding ID = 1 is left as an exercise for the reader.

I assume you just made an example up. If not, you should avoid using reserved words like CHAR as column names.

It's generally considered bad practice for sargability to use a wildcard on both sides of a field. It may not matter in this instance, but if there was an index on that field, it would not be usable and could cause some performance issues.

It may be useful to use SELECT DISTINCT to cover the case where both '1a' and '3c' match the same row (which would otherwise result in the row returning twice).

Related Question