Find a formatted number (phone number, ...) in a MySQL table from a differently formatted search word

Often one has to retrieve a record based on - say - a phone number. The phone numbers in the database may have some layout (e.g. +32 412/123.123.123). A user who types a differently formatted search word (e.g. 412-123 12.31-23, so with different punctuation and without countrycode) still expects to find the correct row in the database.

We use a C++ Builder routine to convert the search word to a regular expression which matches strings that contain all the digits in the search word, with only non-digit characters in between. More specifically in the above example, the search word is transformed into the following regular expression (everything on 1 line of course) :

4[^[:digit:]]*1[^[:digit:]]*2[^[:digit:]]*1[^[:digit:]]*
2[^[:digit:]]*3[^[:digit:]]*1[^[:digit:]]*2[^[:digit:]]*
3[^[:digit:]]*1[^[:digit:]]*2[^[:digit:]]*3

and this matches (independently of the formatting or layout both of the database field and the search word) the correct entry in the database table. Note that in the regular expression, there are only digits and MySQL regex expressions that mean "match an arbitrarily long sequence of non-digit characters". I.e. all punctuation and whitespace was stripped from the search term before interleaving the remaining digits with the string

[^[:digit:]]*

 

The MySQL query is :

SELECT id, name, tel_no FROM customers WHERE tel_no RLIKE ?

(note the RLIKE keyword, which matches regular expressions in MySQL)

The C++ builder code, which is easily modified in any language, is :

    // PhoneNumberToFind is a TEdit control
    AnsiString searchWord = PhoneNumberToFind->Text.Trim(); 
    AnsiString regex = "";
    for (int i = 0; i < searchWord.Length(); i++)
        {
        char c = searchWord[i+1]; // Note AnsiStrings are 1-based
        if (isdigit(c))
            {
            if (regex.Length() > 0)
                {
                regex = regex + "[^[:digit:]]*"; 
                // [...] matches a series of non-digits.
                }
            regex = regex + c;
            }
        }

and regex is then set as the parameter in the above SQL query.

Digon MFS : CRM- en ERP-systeem