From: Peter Brawley
Date: September 25 2007 4:04pm
Subject: Re: more elegant way to store/find phone numbers
List-Archive: http://lists.mysql.com/mysql/209245
Message-Id: <46F93184.5090205@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

 > does anyone have a nicer solution for this?

How about comparing ereg_replace( "[[:punct:]]","", $colvalue ) with 
ereg_replace( "[[:punct:]]","", $comparisonvalue )?

PB

mysql wrote:
> 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?
>
> thanks in advance
>
> suomi
>
>