We help IT Professionals succeed at work.

mysql concatenate question

BR
BR asked
on
Dear Experts,

I select the mobile phone numbers from my database where the length of the mobile number is one missing.
SELECT * FROM `mytable` WHERE length(mobile)='10'

the mobile numbers are supposed to be 11 characters, however I have 170 mobile number's length is 10, starting without 0

what I need to do is to select these 170 numbers and concatenate with 0

how can I do that with mysql?
Comment
Watch Question

Software Team Lead
Commented:
tried use concat function?

like:

SELECT a.*, concat('0', mobile) newValue  FROM `mytable` a WHERE length(a.mobile)=10

Open in new window


to do the update if above is working fine:

Update `mytable` set mobile = concat('0', mobile) where length(a.mobile)=10

Open in new window


CONCAT(str1,str2,...)
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
>>what I need to do is to select these 170 numbers and concatenate with 0

To select this data.

SELECT *
From yourtable
Where len(concat('0', mobile))=11

To update..use below


Update yourtable
Set mobile = concat('0', mobile)
Where len(concat('0', mobile))=11
BRDigital Marketing

Author

Commented:
thank you both