There are two tables in 2 different databases one table consists of Salutations example Mr
, Miss
and other table has names with Salutation example Mr.XYZ
, Miss.ABC
so I need a query which will compare the two tables and gives only those names from table 1 which are of matching salutations.
Example :
x
Table 1 Table 2
Salutation column Name column
Mr., Mr.Abc
Miss., Mr.XYZ
Mr. & Mrs. Mr. & Mrs. Def
Jr. xyz
So the query needs to compare two tables and give the output as
Mr.Abc,
Mr.Xyz
Mr. & Mrs. Def
Advertisement
Answer
I think you want join
query with like
:
select t2.*, t1.salutation
from table2 t2 join
table1 t1
on t2.name like concat(t1.saluation, '%');
The one caveat is that this can return more than one match. For instance, anything that matches “Mr. & Mrs.” would also match “Mr.”. To fix this, you can choose the longest match, using apply
:
select t2.*, t1.salutation
from table2 t2 cross apply
(select top (1) t1.*
from table1 t1
where t2.name like concat(t1.saluation, '%')
order by len(t1.saluation) desc
) t1;