Jump to content

removing special characters


123guy

Recommended Posts

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 :)
Link to comment
Share on other sites

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 by teynon
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.