Elgoots Elgoots - 15 days ago 6
MySQL Question

Replace first number of a phone number mysql

I have a database table

users
that has a column called
mobile
. All the mobile phone numbers start with
0
e.g.:
0455000000


I want to replace ONLY the very first number which is a always a
0
with
61


That would turn the mobile number from
0455000000
to
61455000000


I'm guessing using a trim statement may work but not sure how to implement it.

Answer

MySQL has a convenient function called insert(). This lets you replace a substring by position.

So:

select insert(phone, 1, 1, '')          -- gets rid of the first digit
select insert(phone, 1, 1, 'Mobile #')  -- replaces the first digit with a different prefix