Query to join two tables with like operator

I have two table which has data as below

Table1

+-----+-------+-------------------------+
| ID  | value |          Test           |
+-----+-------+-------------------------+
| 200 |  2456 | yyy xxcv zuio uio tzrue |
| 201 |  2905 | xxx tttt ssrt uio uioe  |
| 203 | 34567 | zzz iii ool uiol werts  |
| 204 |  2356 | xxx tttt ssrt uio wertz |
| 205 |  3478 | io ser xcv erto klop    |
| 206 |  4567 | xxx tttt ssrt uio poiu  |
| 207 |   234 | zzz iii ool uiol wert   |
+-----+-------+-------------------------+

I would like to create where clause to get rows with like operator. for ex.

select *
from Table1
where test like '%xxcv zuio%' Or Like '%iii ool%' OR '%xcv erto%' OR '%ssrt uio%' OR '%uio uioe%'

But my problem is that there are more than 15-20 different parameter in where clause, it can also increase latter.

So I have decided to create a new table in database lets called it Table2 with parameter as shown below.

Table2

+----+-----------+
| ID | Parameter |
+----+-----------+
| 20 | xxcv zuio |
| 21 | iii ool   |
| 22 | xcv erto  |
| 23 | ssrt uio  |
| 24 | uio uioe  |
+----+-----------+

My question is how can I join this table to give the same result like above query.

I am still learning joins so any advice will be helpful.

Answer

You can use exists:

select t1.*
from Table1 t1
where exists (select 1 from table2 t2 where t1.test like concat('%', t2.parameter, '%'))

Advertisement

Answer