Thursday, August 29, 2019

Update phone numbers in MySQL

We have a bug which stores international UK phone numbers with trunk prefix in the database. For instance, the UK number +442079978240 is wrongly stored as 4402079978240. The request is to update these numbers to remove trunk prefix 0 from the database. For above example, update 4402079978240 to 442079978240 removing the first zero.

The thought is to use regexp to do this update, and Mysql does provide regexp operator and regexp_replace, regexp_substr etc function. However, our current db engine doesn't support regexp_xxx series functions, so we have to rely on regexp operator and other avaliable functions to complete this task.

select out matched numbers using regexp
select phone_number from phone_number_table where phone_number REGEXP '^(440)';

preview result
select concat('44',  right(phone_number, CHAR_LENGTH(phone_number) - 3)) from phone_number_table where phone_number REGEXP '^(440)';

update numbers
UPDATE phone_number_table SET phone_number=concat('44', right(phone_number, CHAR_LENGTH(phone_number) - 3)) WHERE phone_number REGEXP '^(440)';

However, as the where clause doesn't have KEY column, so above update SQL will get an error
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

disable safe mode, then preform update

enable safe mode and verify the result
select phone_number from phone_number_table where phone_number REGEXP '^(440)';

No comments:

Post a Comment