myyis
asked on
clean tel number
The tel numbers in my database may have these characters
' ', '-',' (' and ')'
So I use the below in my query to clean them.
SUBSTR(replace(replace(rep lace(repla ce(CE.NUMB ER,' ',''),'-',''),'(',''),')', ''), -10, 10)
Is there a faster alternative ?
Thank you
' ', '-',' (' and ')'
So I use the below in my query to clean them.
SUBSTR(replace(replace(rep
Is there a faster alternative ?
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can test them, but both methods go through the string character by character multiple times.
A simple method that goes through only once would be something like
<?php
$oldstring = "%(913) 285-4589,(812)873-8432";
$newstring="";
for ($i=1;$i<=50;$i++)
{
$char = SUBSTR($oldstring,$i,1);
if (is_numeric($char))
$newstring .= $char;
}
echo "oldstring $oldstring<br>";
echo "newstring $newstring<br>";
exit();
A simple method that goes through only once would be something like
<?php
$oldstring = "%(913) 285-4589,(812)873-8432";
$newstring="";
for ($i=1;$i<=50;$i++)
{
$char = SUBSTR($oldstring,$i,1);
if (is_numeric($char))
$newstring .= $char;
}
echo "oldstring $oldstring<br>";
echo "newstring $newstring<br>";
exit();
ASKER
But I need it on mysql, not php
Ah, sorry, things are so much easier to do in php, I didn't even notice you were restricted to MySQL. Does it HAVE to be in the query? Is there no way you can use php for this?
ASKER
No I have to do it on mysql
myyis:
There isn't a way to do it in query using MySQL. Other databases like Oracle, and SQL-Server, have built-in functions that will do this.
There's a question or two that needs to be answered to best solve this problem:
1) Is this a one-time cleanup, or an ongoing insertion of compromised data into the database.
If it's a one-time-cleanup, then I suggest running a series of update queries to clean up the database. If it's an on-going insertion of bad data, then I strongly suggest the proper course of action is to address the root cause of the problem. While this may encounter resistance from some parties, it ALWAYS turns into one maintenance issue after another. Code must always be added to every query or report to deal with the trash. This is costly, and violates one of the major best practices regarding databases and their content.
Best regards,
AielloJ
There isn't a way to do it in query using MySQL. Other databases like Oracle, and SQL-Server, have built-in functions that will do this.
There's a question or two that needs to be answered to best solve this problem:
1) Is this a one-time cleanup, or an ongoing insertion of compromised data into the database.
If it's a one-time-cleanup, then I suggest running a series of update queries to clean up the database. If it's an on-going insertion of bad data, then I strongly suggest the proper course of action is to address the root cause of the problem. While this may encounter resistance from some parties, it ALWAYS turns into one maintenance issue after another. Code must always be added to every query or report to deal with the trash. This is costly, and violates one of the major best practices regarding databases and their content.
Best regards,
AielloJ
ASKER
The method that I wrote in the question and the alternative of Aaron Tomosky does the job for me.
What I need to learn is which is faster, nothing more, thank you
What I need to learn is which is faster, nothing more, thank you
The proper way is to store only numbers. write a query to clean what you have and update the database. Then add code to your front end so only numbers get added. That way your output query is fast because it doesn't transform anything.
ASKER
Thank you Aaron,
I need the format of the numbers, so I don't have chance to get rid of those symbols.
I only want to hear which method is "theoretically" faster.
Yours or mine?
Thank you
I need the format of the numbers, so I don't have chance to get rid of those symbols.
I only want to hear which method is "theoretically" faster.
Yours or mine?
Thank you
Even if they are close, mine will handle any format you through at it, so it's much safer. Yours is only replacing specific characters.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Which one is faster? Yours?