Jump to content


Photo

removing special characters


  • Please log in to reply
5 replies to this topic

#1 123guy

123guy

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 03 March 2013 - 12:00 PM

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 :)


#2 teynon

teynon

    Advanced Member

  • Members
  • PipPipPip
  • 894 posts

Posted 03 March 2013 - 12:14 PM

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, 03 March 2013 - 12:15 PM.

Support my Kickstarter Project!
http://www.kickstart...7618755/antroid

http://www.thomaseynon.com

Vulnerabilities: http://cwe.mitre.org...x.html#Guidance - MySQL.com hacked with SQL Injection - If it happened to them, it can happen to you.


#3 123guy

123guy

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 03 March 2013 - 12:18 PM

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?



#4 teynon

teynon

    Advanced Member

  • Members
  • PipPipPip
  • 894 posts

Posted 03 March 2013 - 12:20 PM

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


Support my Kickstarter Project!
http://www.kickstart...7618755/antroid

http://www.thomaseynon.com

Vulnerabilities: http://cwe.mitre.org...x.html#Guidance - MySQL.com hacked with SQL Injection - If it happened to them, it can happen to you.


#5 123guy

123guy

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 03 March 2013 - 12:21 PM

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.  



#6 haku

haku

    Advanced Member

  • Staff Alumni
  • 6,177 posts

Posted 03 March 2013 - 10:08 PM

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com