Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find telephonenumbers - finding number with and without an phone extension

I've a table with about 130 000 records with telephonenumbers. The numbers are all formated like this +4311234567. The numbers always include international country code, local area code and then the phonenumber and sometimes an extension.

There is a webservice which checks for the caller's number in the table. That service works already. But now the client wants that also if someone calls from a company which number is already in the database but not his extension, that the service will return some result.

Example for table.

   **id** | **telephonenumber**    | **name**   
|    1    | +431234567             | company A  
|    2    | +431234567890          |  employee in company A  
|    3    | +4398765432            | company b 

now if somebody from company A calls with a different extension for example +43123456777, than it should return id1. But the problem is, that I don't know how many digits the extensions have. It could have 3,4 or more digits.

Are there any patterns for string kind of matchings?

The data is stored in a sql2005 database.

Thanks

EDIT:
The telephonenumbers i am getting from a crm system. I've talked with the admin of the crm and he is trying to send me the data in a different format.

   **id** | **telephonenumber** |**extension**   | **name**   
|    1    | +431234567          |                | company A  
|    2    | +431234567          |      890       |  employee in company A  
|    3    | +4398765432         |                | company b 
like image 328
nWorx Avatar asked Dec 28 '22 17:12

nWorx


1 Answers

Is there a way to determine which exact part of the stored number is an extension? Or are the "base" numbers without extansion are stored. IF yes you could just check if a number in your database(without extension) is a prefix of the current number to check. Prefix means a substring of the String that starting at the beginning.

But if you have only numbers in your database with extension and there is no way to find out how many digits belong to it, I believe you can not find an exact solution.

like image 99
HansDampf Avatar answered Dec 31 '22 07:12

HansDampf