'MYSQL: replacing a phonenumber to add a countrycode

I have a table with phonenumbers that are not correct.
Some have numbers like: 06xxxxxxxx
Others have numbers like: 06-xxxxxxxx
I want them like this: +316xxxxxxxx

My table:
userid, phonenumber, country

My current (non-working query) query:
UPDATE account_number am
SET am.phonenumber = REPLACE(am.phonenumber,'06','+316');

The above will not work, because if someone would have a number like +31611061111 it will also be replaced and become +31611+3161111

I hope my question is not to complicated.
Thanks in advance...

mysqlreplacephone-number


Solution 1:[1]

Try something like

UPDATE account_number am
SET am.phonenumber = '+316' + Substring(am.phonenumber,3,Length(am.phonenumber) - 2)
where am.phonnumber like '06%'

Solution 2:[2]

You can use WHERE clause to match numbers that only start with 06 if that's what you're after. To achieve that use LIKE

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Moseleyi

Related Questions