Selectively removing whitespace from a string with SQL

I have two columns of adresses that I need to join. Column 1 has the adresses formatted as "Some Street 12 A" while column 2 has them formatted as "Some Street 12A."

Ie. the last whitespace between 12 and A is removed in one of the columns. Is it possible to write an SQL condition that recognises the white space following a number when doing a database join?

I can't count white spaces since some street names have white space in them and some don't.

If you have a software specific solution, I have access to QGIS and ArcGIS Desktop (most basic license, whatever it's named at the moment.) If you have a solution in either of these software packages even if it doesn't use SQL, that's fine as well.