Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

clean tel number

Posted on 2014-03-22
12
Medium Priority
?
278 Views
Last Modified: 2014-03-25
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(replace(replace(CE.NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)

Is there a faster alternative ?
Thank you
0
Comment
Question by:myyis
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 1000 total points
ID: 39948179
Don't think of it as cleaning a phone number, think of it as selecting all numbers or removing all non numbers
http://sqlblogcasts.com/blogs/simons/archive/2008/02/26/String-cleaning-with-TSQL.aspx
0
 
LVL 1

Author Comment

by:myyis
ID: 39948446
Thank you for the method you've posted.

Which one is faster? Yours?
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39948524
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();
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 1

Author Comment

by:myyis
ID: 39948528
But I need it on mysql, not php
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39948530
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?
0
 
LVL 1

Author Comment

by:myyis
ID: 39948537
No I have to do it on mysql
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 39948593
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
0
 
LVL 1

Author Comment

by:myyis
ID: 39948661
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
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39948772
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.
0
 
LVL 1

Author Comment

by:myyis
ID: 39948803
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
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39948818
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.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 39950381
Your solution is THE FASTEST by far as long you know for sure that you only have to eliminate a small number of characters, 3 in this case, and what exactly those are. The reason ia that the REPLACE is an internal function built special for this purpose and is quite efficient. When it comes to regular expression you can do more powerful things but it will be a bit slower, especially if you use xml tricks, which are actually meant for other purposes. It will work, however.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month6 days, 8 hours left to enroll

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question