Join condition with like %

Question

Hi there,

How to put condition on on clause when joining two tables-

select * from a
left outer join n
on
a.[Visit ID] =n.[Service Event ID]
and
a.[Appt Date] =n.[Service Date]

eg, visit id be like '%n.[Service Event ID]%'

Answer 1

Hi @ASHMITP
Two points to note:

1. Wrong syntax: aaa ='%aaa%'
2. Long strings come before like, short strings come after like.

Try change this app.[Visit ID] ='%' + nap4.[Service Event ID] + '%' to this nap4.[Service Event ID] LIKE '%' + app.[Visit ID] + '%'
Also, you can use CONCAT, like this : ON nap4.[Service Event ID] LIKE CONCAT('%',app.[Visit ID],'%')

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can, but you have to do it the right way by concat the column with two apostrophe, like

select * 
from a
left outer join 
n
    on a.[Visit ID] = '%' + n.[Service Event ID] + '%'
       and a.[Appt Date] =n.[Service Date]