nads nads - 1 year ago 68
MySQL Question

MySQL: How do I search and replace chars at the beginning of a string

I'm trying to search and replace mobile numbers with the full international code.
So where rows have 07970000007 to replace the beginning with +447970000007

UPDATE tblMemberImportClub
SET msisdn = REPLACE(msisdn, '07', '+447')
WHERE INSTR(msisdn, '07') = 1;

But this also replaces the other matches:


I don't think i can use TRIM as some rows will already start with +447 and will therefore nor require any updates.

Thanks in advance for any assistance.

Answer Source

Use LIKE and INSERT():

UPDATE tblMemberImportClub 
    SET msisdn = INSERT(msisdn, 1, 2, '+447') 
    WHERE msisdn LIKE '07%';

INSERT() is a string function that replaces exactly the characters you specify (see here).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download