we have a mysql based application, wherein phone numbers may be stored
and searched for. it is not the primary goal of this application to
handle phone numbers.
phone numbers usually are entered in a form like 099 999 99 99 or
099-999-99-99, or substings thereof. actually, the application stores
the phone numbers as they are entered in a text field.
a user searching an entry by phone number, on a first sight, cannot
know, how the number is stored. i therefore leave him the freedom to
enter the (sub) search like "99 99" or "99-99". as i am in php, no
problem to extract the space or the dash using regular expressions.
regular expressions in mysql, however, do not return collected
subresults, they only return, whether there was a match or not. so, in
order to compare the above (sub) search with the stored phone number, i
constructed the following ugly query:
select columns from table where REPLACE(REPLACE(phone, '-', ''), ' ',
'') REGEXP sub_search;
does anyone have a nicer solution for this?
thanks in advance