CID Lookup Source - Pattern Matching?

General Help
1

Would like to suggest one possible update to the way CID Lookup source works (unless its already there, in which case, a gentle pointer would be highly appreciated). Would it be possible to have the number matching based on a pattern starting from the right, not entire number. I have multiple incoming DIDs from different providers. Depending on which DID folks call, their CLI appears with or without the international dialling code, with or without the preceding 0 etc. The same number can appear as:

021 123 456
21 123 456
92 21 123 456
+92 21 123 456.

I think Cellphones do number to phone book entry matching like this, allowing numbers to be matched to names irrespective of the prefix on an incoming number.?

2

or this:

http://databases.aspfaq.com/database/how-do-i-ignore-common-words-in-a-search.html

3

Good idea.

In my case the CID is fine, but system “standard” isn’t a standard. Lots of data sources and inconsistencies.

Definitely the strategic solution would be to fix the data so that there is a standard and enforce it, but in the short term…

The fact is a phone number is a sequence of numbers with arbitrary punctuation and spacing, with the most significant portion being to the right. If the punctuation is stripped during the comparison, then it does away with the need to standardise.

4

Would this take care of the spacing issue:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&tid=1a01a4a7-ac6a-4c39-9933-04e2ec1dbd82&p=1

Suppose we could compare against Len(CID)-2 or 3 characaters to get rid of any prefixes?

5

because different providers handle this differently, the best way to address it is to have each trunk come into it’s own context (instead of from-pstn). In that context you would normalize the cid to your system standard and then send the call on its way to from-pstn where the normal FreePBX processing will handle it as usual.

6

Yes it is.

I haven’t figured this out entirely, but it is possible that there can be inconsistencies in the number stored in the db (which is my case) and the number coming in on CID.

In either case, the data needs to be normalised before the match can take place. With a phone number, this means stripping out the non-numeric parts of the number, then doing a substring match with the rightmost portion of the number.

In the worst case scenario, both numbers have punctuation, and different portions of the number are in the DB and through CID.

Lets say that the real, full number is “+61 2 6061 1441”

And lets say that the DB has "02-6061-1441"
And the CID that comes in is “6061 1441”

What would need to happen is the non-numerics are stripped from both numbers then a substring match for the right most portion of the number for the length of the shorter of the two numbers.

I think this can be done with some regex and a substring match in mysql - perhaps a mysql expert can shed some light?

7

Hello?..not worth the effort to implement this??