How to help implement more logic in my joins?

I have some logic currently in my sql statement that takes away all punctuation so I can compare the two fields easily; however, I am having an issue now where my A table has a bank name of BBVA BANCOMER SA INSTITUCION DE BANCA MULTIPLE, GRUPO FINANCIERO BBVA BANCOMER and my B table has the Institution name of, BBVA BANCOMER and other variations that do not match Table A.

I have multiple different bank names with this issue.

How do I add a like '%<bank name here>%' into my join to find all the matches? Thank you.

SELECT B.RECORD_KEY, A.[R.F.C.], A.[Bank Name]
FROM srcENRICH.dbo.FICO_BNKA_RFC_E AS A
INNER JOIN dbo.BM_BNKA_SRCENRICH_BM_SWIFT_S AS B
   ON REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(sA.[Bank Name], ',', ''), '.', ''), '-', ''), '+', ''), '/', ''), '''', ''), '&', 'AND'), '(', ''), ')', '') 
                    = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(B.INSTITUTION_NAME, ',', ''), '.', ''), '-', ''), '+', ''), '/', ''), '''', ''), '&', 'AND'), '(', ''), ')', '')

I was thinking maybe a WHERE clause; however, I don’t know how to compare the two fields together based on a LIKE function. If I found a solution for this, would I need to do the same for each bank needed?

---

I am currently getting 31 matches soley based off of taking punctuation out; however, my expected output is above 1,650. This is because BANCOMER has no matches anywhere solely based on exact name, however, when I go into Table B database, there are ~1600 outputs for LIKE '%BANCOMER%'. I need to implement a LIKE function.

Answer

I think what you need is a lookup table that gives you like values. Then you can populate that table in a way that drives your business rules.

For example consider the following LOOKUP table.

CODE VALUE
---- -----
1    BOA
1    BANK OF AMERICA
2    CITIBANK
2    CITI

Then you can have a query like this:

 SELECT *
 FROM TABLEA A
 JOIN LOOKUP ALOOK ON A.BANK = ALOOK.VALUE
 JOIN LOOKUP BLOOK ON ALOOK.CODE = BLOOK.CODE
 JOIN TABLEB B     ON B.BANK= BLOOK.BANK

Now we have A and B joining on BOA = BANK OF AMERICA and CITIBANK = CITI

You can continue to add new entities to the LOOKUP table as you “find” them and want them to join together.

Using a lookup table in this way on SQL servers is much faster than running a function (or multiple) like you are in the sample code.