Wildcards and Joins

[simon_alfie]

Hi all,

I have come accross a problem with a database I am using.

There are two tables (A and B) with fields that contain phone numbers. I want to join these two tables where the phone numbers are the same number. The problem is that the numbers are entered in one of two different formats.

The first format is the national format e.g. 1800555555 for a US number or 01777123123 for a UK number. The second format is the international format +1800555555 for a US number or +441777123123 for a UK number. The formats are both used in the fields in each table. The numbers are stored as text.

So to join the tables i would like to just match on the last 8 digits of the number. Is there a way to do this? Can you use wildcards to select a poriton of the field to match on for the join? e.g. "*????????"

Thanks in advance.

[simon_alfie]

If anyone is interrested I worked out how to do this in Access.

SELECT * FROM a LEFT JOIN b ON RIGHT(a.phoneNumber,8)=RIGHT(b.phoneNumber,8);

The string manipulator function RIGHT selects the last X characters of a string.