1

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.

0
2

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).

0
2

Below is the solution that will work on Microsoft SQL Server. You may want to have a clustered index on the t2.TXT column. Also test using below two approaches in ON condition.

  1. t2.TXT LIKE '%' + t1.NAME OR t2.TXT LIKE t1.NAME + '%'
  2. t2.TXT LIKE '%' + t1.NAME + '%'

I guess the first one will work more better in terms of performance.

SELECT t2.TXT       -- Or any required columns 
FROM t1
INNER JOIN t2
    ON (t2.txt like '%' + t1.NAME) 
       OR (t2.txt like t1.NAME+ '%')
where t2.OPTIONID = 'A'
    AND t1.ID = 1   -- based on user

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.