How to remove non-numeric characters from MySQL cells

Question

I have a table with ~200,000 rows. There are three different phone number columns and the data in them is not all formatted the same. I'd like to remove any value that is not a number and update every cell.

For instance, (412)641-5892 becomes 4126415892.

I found this STRIP_NON_DIGIT() function here. I can use that in my SQL queries and it works properly, but it takes a minute to return a result. I'd like to run a mass UPDATE across the entire table, but not sure what the syntax is for that.

Something like this is what I'm going for.

UPDATE leads 
SET phone    = STRIP_NON_DIGIT(phone), 
mobile_phone = STRIP_NON_DIGIT(mobile_phone), 
home_phone   = STRIP_NON_DIGIT(home_phone)

possible duplicate of http://stackoverflow.com/questions/18660482/how-to-remove-all-the-non-numeric-characters-from-column-in-mysql-in-bulk-data see if that is what you need — brad, Apr 12 '17 at 16:36
Make sure you fix the future before you fix the past. That way you only have to fix the past once. — Dan Bracuk, Apr 12 '17 at 17:09
@Shadow I just thought I was writing pseudo code! Just ran it and it seems to have worked perfectly. — Anthony Garand, Apr 12 '17 at 18:00

score -1 · Accepted Answer · answered Apr 17 '17 at 14:57

-1

Turns out the answer was the pseudo code that I wrote!

answered Apr 17 '17 at 14:57

Anthony Garand

306
3
5
16

How to remove non-numeric characters from MySQL cells

1 Answers1