Regex - Remove 0 and Add Country Code

[kevinkhan]

i have alot of numbers with 10 digits like this

0987654321

I want a sql query that will remove the 0 and add 353.

Is there any body that can help me with this please?

[Barand]

this doesn't use regex but

mysql> SELECT id
    -> , name
    -> , phone
    -> , CASE
    ->     WHEN SUBSTRING(phone,1,1)='0'
    ->         THEN CONCAT('(353)',SUBSTRING(phone,2))
    ->     ELSE phone
    ->   END as phone2
    -> FROM contact;
+----+-------------+-------------+-----------------+
| id | name        | phone       | phone2          |
+----+-------------+-------------+-----------------+
|  1 | aaaaaaaaa   | 12345674321 | 12345674321     |
|  2 | bbbbbbbb    | NULL        | NULL            |
|  3 | cccccccc    | 01214567890 | (353)1214567890 |
|  4 | dddddd      | NULL        | NULL            |
|  5 | eeeeeeeeeee | 09087654321 | (353)9087654321 |
|  6 | kkkkkkkkk   | NULL        | NULL            |
|  7 | mmmm        | NULL        | NULL            |
+----+-------------+-------------+-----------------+

[PravinS]

You can use UPDATE query like this

UPDATE table_name SET phone = CONCAT('353',TRIM(LEADING '0' FROM phone)) WHERE SUBSTRING(phone,1,1) = '0';

before that use the SELECT query to check if its correct or not

SELECT phone, CONCAT('353',TRIM(LEADING '0' FROM phone)) FROM table_name WHERE SUBSTRING(phone,1,1) = '0';

hope this will find you helpful