MySQL Advanced Search and Replace Using String Functions

I had a table with 6,000 entries including three phone number columns. Many of them were formatted like this:

(555) 555-1212

or like this

5555551212

or some such variant.

I wanted them like this:

555-555-1212

I used the following SQL statement to do the search and replacement:

UPDATE `requestsnew`
SET
`request_client_phone`=INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_client_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-'),
`request_hospital_phone`=INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_hospital_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-'),
`request_pickup_phone`=INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_pickup_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-'),
`request_dropoff_phone`=INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_dropoff_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-')
;

I had another table with just one column that needed to be fixed the same way. I used the following SQL statement.

UPDATE `trip_history`
SET
`trip_phone`=INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(`trip_phone`), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-')
;

Note that the INSERT() in the statements above is a string INSERT() function that has nothing to do with the basic SQL INSERT statement. One is a function, and the other is a statement.

What the SQL above does to each phone number is trim it of any leading and trailing spaces (the innermost function), then remove any '(', then remove any ')', then remove any ' ' (space), then remove any '-' (hyphen), then stick in the standard hyphens. That's a trim(), four replace()s, and two insert()s.

Before I ran the update to do the search and replace, I tested it with a SELECT like this:

SELECT
INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_client_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-') AS a,
INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_hospital_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-') AS b,
INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_pickup_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-') AS c,
INSERT(INSERT(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(request_dropoff_phone), '(', ''), ')', ''),' ',''), '-', ''), 4, 0, '-'), 8, 0, '-') AS d
FROM `requestsnew`
;

For SQL beginners:

Note that the ` (reverse quote) characters around the table and column names are optional if those names have no spaces or other need to be explicitly delimited. I omitted them in part of the first UPDATE SQL.

Note that the AS statements make my column headings nice and short in the SELECT result. They could also be used to assign associative keys to the result of a query if you were using it in, say, PHP.

Feel free to comment or question me.