Jump to content

Regex - Remove 0 and Add Country Code


Recommended Posts

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            |
+----+-------------+-------------+-----------------+
  • Like 1
Link to post
Share on other sites
  • 2 weeks later...

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

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.