Post reply

how to join two tables having no common row and no primary key in any of the two table

  • April 17, 2008 at 2:35 am

    #803522

    This is a non-trivial problem. It's not entirely clear what the OP wants but if it's as Jeff suggests it's going to be tricky. We do a similar thing here - we have dirty phone number data directly entered by customers as text that we need to match against outbound call data from a PABX to correlate calls to customers. The process is basically as follows:

    We have a table containing all known countries along with both their country and area prefixes (for dialling INTO that country from elsewhere), and also their IDD codes (for indicating an international call when dialling OUT of that country to elsewhere).

    The dirty customer phone numbers can potentially contain any perversion of the customer's real phone number (assuming it's not outright typo'd in the first place:

    - country code + area code + phone number

    - area code + phone number

    - phone number

    Sometimes the customer "helpfully" puts their local IDD code in as well, which of course is different depending on their country of residence and even network.

    So we do - (assumption: we already know what country the customer resides in):

    - Strip any known IDD codes from the front of the phone number for their country of origin (if any)

    - Determine whether the remaining phone number already contains the country + area code, area code, or neither.

    - Add the missing area code and/or country code if needed.

    - Strip the IDD code(s) from the front of the PABX dialled numbers, and compare the two sets.

    The hard part was actually collecting and collating all of the country/area/IDD code data from various sources and putting it into the table. There are unfortunately some prefixes that overlap others, especially with some networks using in small or recently created nations. It's a fuzzy process at best...

    I'm not sure the OP is trying to do exactly this, but it's probably going to be quite painful unless they are working with a small known subset of numbers (eg only Pakistan).

    Regards,

    Jacob

  • April 18, 2008 at 8:22 am

    #804392

    Good post, Jeff. It is posts like this that makes daily watching of this forum worthwhile.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply