Cheatsheet for common data cleansing with mySQL (Phone Number, NIK, dsb)

cleansing.sql

SELECT * FROM table WHERE phone_number REGEXP '^\\+62';

UPDATE table
SET phone_number = REPLACE(phone_number, '+62', '0')
WHERE phone_number REGEXP '^\\+62';


#######

SELECT * FROM table WHERE phone_number REGEXP '-';

UPDATE table
SET phone_number = REPLACE(phone_number, '-', '')
WHERE phone_number REGEXP '-';

#######


SELECT * FROM table WHERE nik REGEXP ' ';

UPDATE table
SET nik = REPLACE(nik, ' ', '')
WHERE nik REGEXP ' ';