pandemic pandemic - 4 months ago 21
SQL Question

Ho to strip and replace values in mysql

I have around 200,000 records of data with phone numbers, but the numbers are inconsistent.

for example, some may be 10 digits (missing a 0 at the beginning), some have spaces in there, some have a '-' in the middle and some begin with '+44' instead of 0.

Is there a way in mySQL to condition all these and cleanse the data in one query?

Answer

Without sample data and without an example output this is purely speculative and assuming you want the output in the format of 01234567891.

Use a combination of LENGTH, REPLACE' ANDLEFT` functions to resolve the 4 issues you highlighted:

  1. Missing 0 at beggining.
  2. Spaces in the string.
  3. -'s in the string.
  4. +44 rather than 0.

SELECT CASE WHEN LENGTH(REPLACE(REPLACE(numberfield, '-', ''), ' ', '')) = 10 
                THEN CONCAT('0', REPLACE(REPLACE(numberfield, '-', ''), ' ', ''))
                WHEN LEFT(REPLACE(REPLACE(numberfield, '-', ''), ' ', ''), 3) = '+44'
                THEN REPLACE(REPLACE(REPLACE(numberfield, '-', ''), ' ', ''), '+44', '0'
           END AS Cleannumber
    FROM yourtable
Comments