removing special characters

[123guy]

ok, so I have a database of customer contact info that is sent to me from my boss.  There are phone numbers that are in different formats, 123-456-7890, (123) 456-7890, or 1234567890.  I am building a query system for this info, and I have been able to strip the special characters on a query string.

 

$Phone = preg_replace('/\D+/', '', $Phone);

 

my issue is when I query the database using PHP, how do I manage to make it so that all phonenumbers that it compares, will be in that format, just one continuous string with only numbers?  any help is appreciated

[teynon]

I would focus on normalizing your database first.

 

You could try to run an update query to normalize what you have.

 

UPDATE phonetable SET phone = CONCAT(substring(phone, 0, 3), substring(phone, 4, 3), substring(phone, 8, 4)) WHERE tblid IN (SELECT tblid FROM phonetable WHERE phone LIKE '___-___-____')

 

I haven't tested the above query, you should create a backup table and run those queries in a test environment first. There may be better ways of normalizing the database, but off the top of my head, the one above might work.

Edited March 3, 2013 by teynon

[123guy]

I thought about updating the table, but there is going to constantly having phone numbers added to the database, so i would have to run that query every hour or so.  is there anyway to do it when I select from the database?

[teynon]

Then you should focus on fixing the script that is inputting those phone numbers.

[123guy]

I can't.  my boss has a website where he has clients put in their phone number and he believes in allowing them to put them in however they feel fit.  he then exports it in a csv doc to me, and I just upload it to the database using the csv.  

[haku]

Letting the user put in the number however see fit is definitely the best way to do it. After the user has submitted a value, the back-end script should format the number as necessary.

 

Or, if you are querying the database, you can use something like this (MySQL):

$phone_number = 12345678;
$query = 'SELECT phone_number FROM phone_numbers WHERE REPLACE(' ', '', phone_number) = ' . $phone_number