> hi listers
> 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?
You could create an extra 'standardised' column that is used for searches. I
discussed this in another post last week -