List:General Discussion« Previous MessageNext Message »
From:mysql Date:September 25 2007 3:26pm
Subject:more elegant way to store/find phone numbers
View as plain text  
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

Thread
more elegant way to store/find phone numbersmysql25 Sep
  • RE: more elegant way to store/find phone numbersEdward Kay25 Sep
  • Re: more elegant way to store/find phone numbersPeter Brawley25 Sep