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
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 :
AnsiString searchWord = PhoneNumberToFind->Text.Trim(); // PhoneNumberToFind is a TEdit control
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.