This is not working with the % but it does work without it.
SELECT ,[Name] ,[Address] ,[Address2] ,c.[City] ,r.[city_name] ,[State] ,[ZipCode] ,[TerritoryCode] ,[CountryCode]
FROM [Customer] c
left join [xRefCityNames] r ON c.Address2 = r.[city_name]
where c.city = '' and address2 = 'new delhi'
This works but when I use ON c.Address2 = '%' + r.[city_name] + '%'
I get NULL for city_name.