nads nads - 1 month ago 7
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:

+4479700000+447

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

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).