CODEWITHSUNDEEP
mysqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

How to add 0 (zero) for mobile number

On the mobile field I have a list of mobile number starting with 7xxxxxxxxx

I need a quick way to add 0 at the beginning, eg: 07xxxxxxx

But it will need to check if 0 exist or not before updating

Is it possible to do that by SQL query

Upvotes: 0

Views: 1970

Answers (3)

Thilo
Thilo

Reputation: 17735

UPDATE table
SET mobile = CONCAT('0', mobile)
WHERE mobile NOT LIKE '0%'

Upvotes: 4

ajreal
ajreal

Reputation: 47321

let's say the mobile field is a 9 digits (including the leading zero),
here is an example :-

mysql> select lpad('123456789', 9, 0);
+-------------------------+
| lpad('123456789', 9, 0) |
+-------------------------+
| 123456789               | 
+-------------------------+
1 row in set (0.00 sec)

mysql> select lpad('12345678', 9, 0);
+------------------------+
| lpad('12345678', 9, 0) |
+------------------------+
| 012345678              | 
+------------------------+
1 row in set (0.00 sec)

Upvotes: 0

user330315
user330315

Reputation: 

UPDATE the_table
    SET the_phone_number = '0'||the_phone_number
WHERE the_phone_number NOT LIKE '0%'

This assumes that MySQL is configured with ANSI mode (because of the standard concatenation operator ||)

Upvotes: 0

Related Questions