CODEWITHSUNDEEP
sql
marioosh
marioosh

Reputation: 28556

SQL: expading "in (...)" to "like"

I have a sql select like below:

select * from table1 where text in (select text from table2)

In real in in clausule is more complex select that this. text is string (varchar). How to expand that sql to select rows from table1 where text is like texts from table2 (not only exactly equals) ?

Upvotes: 3

Views: 821

Answers (3)

Robert Sievers
Robert Sievers

Reputation: 1355

I know I am late to the party here, but you can also just do this with a JOIN if you need the wildcard text as part of the query (as I did). Assuming you have all the entries to check against in table2.text, 

SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON t1.text LIKE '%' + t2.text + '%'

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

If you have your wild card expressions in the text column in Table2 you can do like this.

select *
from Table1 as T1
where exists (select *
              from Table2 as T2
              where T1.[text] like T2.[text])

Otherwise you need to add the % in your query.

select *
from Table1 as T1
where exists (select *
              from Table2 as T2
              where T1.[text] like '%'+T2.[text]+'%')

Upvotes: 6

Oleg Dok
Oleg Dok

Reputation: 21756

Something like this:

select * 
from table1 
where exists 
  (
    select *
    from table2 
    where table1.text like N'%'+table2.text+N'%' 
  )

Note

This may be a performance killer

Upvotes: 1

Related Questions