Replace phone number prefix mysql

I need to select and replace all Phone NO prefix (0122) with new number prefix (072) like

01220000000 to 0720000000

but my table has many format of Phone NO

('+11201220000000' , '+12201220000000@abc.com' or '01220000000/01220000001').

I tried to use REGEXP to find Phone NO have format like ^/0122/[0-9]{1,7}$, but I have problem with

01220000000/01220000001.

How should I update all Phone NO by one query using Mysql

Solution

You seem to be on the right track by using REGEXP. Here is how I would phrase the update:

UPDATE yourTable
SET phone = REPLACE(CONCAT('072', SUBSTRING(phone, 5)), '/0122', '/072')
WHERE phone REGEXP '^[0+]122';

This answer should work assuming that, for the cases where two numbers appear, those numbers are always separated by forward slash and the first number would be a candidate for updating whenever the second number is also a candidate.

Note that your data is not normalized. It isn't good database design to have fields containing more than one phone number.

Replace phone number prefix mysql

Demo