> 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 - http://lists.mysql.com/mysql/209197 Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
