How can I define a replacement pattern in SQL?

serious database n0ob here,

My question is as follow: I want to update a table in Access with another table using a join. However, the columns who make up the join between the two tables might not be perfectly the same. For example, one field might be AAA-000 and its corresponding field AAA/000.

开发者_C百科

Now I come from Mathematica where we could simply define rules indicating that some things could be considered the same, for example the "/" and the "-" characters would be "isomorphic"...

How can I tell Access (or SQL for that matter) how to consider a set of characters as the same? (In the same way "a" and "A" would be the same because if it were case insensitive)

Thanks a lot

EDIT : And if possible, how slower would such a query would be? Is it better anyway to just clean both tables in Excel?

---

As far as I can tell, you can't do that in Access. What you need to do, is transform one source into the other while doing joins, or just normalize both tables before joining them. For example, if the source field, say Name had the value aaa-000 and you wanted to transform it into AAA/000, you could use the following:

UCase(Replace([Name], "-", "/"))