How to put a character in the middle of a string in MySQL-CodePudding

I have emails like this in the users table.

I need to change it like this.

It means need to add sign between the number and the srtings. how can I do that?

CodePudding user response：

If your strings follow that exact pattern, i.e., always starting with TestMail, then you could just use REPLACE() documented here.

UPDATE USERS_TABLE
   SET EMAIL_ADDRESS = REPLACE(EMAIL_ADDRESS, 'TestMail', 'TestMail ')
 WHERE EMAIL_ADDRESS LIKE ('TestMail%);

Otherwise, you might need to get more complex with REGEXP_REPLACE(), e.g.,

UPDATE USERS_TABLE
   SET EMAIL_ADDRESS = REGEXP_REPLACE(EMAIL_ADDRESS, '^:alpha:', 'TestMail ')
WHERE EMAIL_ADDRESS LIKE ('TestMail%);

But I'll assume the format is as you described for your "TestMail" users.