-- Step 1 - This query will remove all non-digits (0-9) from the phone number
UPDATE users SET phone_number = REGEXP_REPLACE(phone_number,'[^0-9]', '');

-- Step 2 - Now we remove the first digit, if it's 1 (for the country code for USA/Canada)
UPDATE users 
SET phone_number =  CASE 
                        WHEN SUBSTR(phone_number, 1,1) = '1' THEN SUBSTR(phone_number, 2,LENGTH(phone_number) -1) 
                        ELSE phone_number 
                    END;

-- Step 3 - Where ever the Phone Number is longer than 10 digits, take any digits after 10, and put them in the extension column
UPDATE users 
SET extension = SUBSTR(phone_number, 11, LENGTH(phone_number) - 10) 
WHERE LENGTH(phone_number) > 10;


-- Step 4 - Cleans the extension off the end of the phone number column
UPDATE users
SET phone_number = SUBSTR(phone_number, 1,10);

-- XXX-XXX-XXXX
UPDATE users SET phone_number = CONCAT(SUBSTR(phone_number,1,3),'-',SUBSTR(phone_number,4,3),'-',SUBSTR(phone_number,7,4))

-- XXX.XXX.XXXX
UPDATE users SET phone_number = CONCAT(SUBSTR(phone_number,1,3),'.',SUBSTR(phone_number,4,3),'.',SUBSTR(phone_number,7,4))

-- (XXX) XXX-XXXX
UPDATE users SET phone_number = CONCAT('(',SUBSTR(phone_number,1,3),') ',SUBSTR(phone_number,4,3),'-',SUBSTR(phone_number,7,4))

/* 
    Step 1 - Copy the dirty unit numbers over to the unit_number column
    
    REGEX_SUBSTR will basically use a regex pattern to find the first occurance of a regex pattern in a string.

    In this case, we're looking for 
    
    ^ - start of string
    (unit|apt|no) - one of these options
    .{1,2} - one or two characters of any kind.  Usually will be just a space, or a comma then a space
    [0-9]+ - one or more digits (0-9)

    example: "apt. 285, 81001 Klocko Crossroad, Haleyhaven, Indiana" will return "apt. 285"
*/
UPDATE users SET unit_number = REGEXP_SUBSTR(address, '^(unit|apt|no).{1,2}[0-9]+');


/* 
    Step 2 - Now we're going to remove the unit number from the address field

    This query just snips the length of unit number off the front of the address column
*/
UPDATE users 
SET address = SUBSTR(address, LENGTH(unit_number)+1, LENGTH(address)-LENGTH(unit_number)) where unit_number IS NOT NULL;

/*
    Step 3 - Now we clean up the unit number field
    
    In this one, we're just looking for instances of grouped digits.

    example: "apt. 285" will return "285"

*/
UPDATE users 
SET unit_number = REGEXP_SUBSTR(unit_number, '[0-9]+') where unit_number IS NOT NULL;

/*
    Step 4 - Clean up the address field

    When we do step 2, it often leaves some junk at the beginning of the string.
    Eg: "apt. 285, 81001 Klocko Crossroad, Haleyhaven, Indiana" 
         turns into ", 81001 Klocko Crossroad, Haleyhaven, Indiana"

    REGEX_INSTR() will return the position of the first regex match
    so we look for the next number and chop a bit more off the address field if there is any junk left
*/

UPDATE users 
SET address = SUBSTR(address, REGEXP_INSTR(address, '[0-9]+'), LENGTH(address) - REGEXP_INSTR(address, '[0-9]+'));

-- Step 5 - We basically the step 3 function again on the address to build the street_number column
UPDATE users SET street_number = REGEXP_SUBSTR(address, '[0-9]+');

-- Step 6 - Same function as step 2, just to clean the street number off of the address field
UPDATE users SET address = TRIM(SUBSTR(address, LENGTH(street_number)+1, LENGTH(address)-LENGTH(street_number)));

-- Step 7 - Now we're going to update the remaining fields with basic string functions
UPDATE users SET street = SUBSTRING_INDEX(address, ",", 1); -- fill address
UPDATE users SET address = TRIM(SUBSTR(address, LENGTH(street) + 2, LENGTH(address) - LENGTH(street))); -- remove street from address

UPDATE users SET city = SUBSTRING_INDEX(address, ",", 1); -- fill city
UPDATE users SET address = TRIM(SUBSTR(address, LENGTH(city) + 2, LENGTH(address) - LENGTH(city))); -- remove city from address

UPDATE users SET state = SUBSTRING_INDEX(address, ",", 1); -- fill state