Can I fix phone number formatting in my database using an update query in Coldfusion?-CodePudding

I have a customer table (customer_table) in my database that has a cell phone column (cell_phone). Before I had input masking implemented in my HTML form, the values were being entered in the database without any standard formatting mask.

Here is an example of some data:

Customer_A: 7325555555

Customer_B: 1732-555-5555

Customer_C: 17325555555

Customer_D: 1-732-555-5555

Customer_E: 732-555-5555

I want to fix all this data and have all the numbers follow this format: 732-555-5555

Is there a way to run an update query on this table and add the hyphens and strip the proceeding '1' for the data in the column that need updating?

CodePudding user response：

You can use a regular expression to strip any non-digits, use the rightmost 10 characters of the result and build the new phone number by concatenating substrings and hyphens:

UPDATE customer 
 INNER JOIN (SELECT id, right(regexp_replace(phone, '[^0-9]', ''), 10) as phone 
               FROM customer) c 
    ON customer.id = c.id
   SET customer.phone = concat(substr(c.phone, 1, 3), '-',
                               substr(c.phone, 4, 3), '-',
                               substr(c.phone, 7));

Here's a fiddle