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

ray.one 0
Viewed 40

I have emails like this in the users table.

TestMail20000@gmail.com

I need to change it like this.

TestMail+20000@gmail.com

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

mysql sql
1 Answers

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.

useraDi40
Related