Question 55664 in Mysql Question posted in
The official MySQL documentation can be found here.

In MySQL, how do I do a bulk update to a LIKE query, removing a character?

I have the following MySQL query that pulls back 400 records:

SELECT email FROM user WHERE email LIKE 'GMSD%@law.com'

Returns:

[email protected] through [email protected]

What I want to do is remove the K from all 400 of these e-mail addresses in one easy UPDATE, so the return would be [email protected] through [email protected].

How can I do this from a LIKE query? Using RegEx somehow?

Tags:

2

Answers


  1. 0

    As the where clause only finds mails with GMSD at the start and @law.com at the end, you should not have any side effects

    CREATE tABLe user (email varchar(100))

    INSERT INTO user VALUEs('[email protected]'),('[email protected]')

    UPDATE user
SET email = REPLACE(email,'GMSDK','GMSD') WHERE email LIKE 'GMSD%@law.com'

    Rows matched: 2  Changed: 2  Warnings: 0

    SELECT * FROM user
    email
    [email protected]
    [email protected]

    fiddle

    if you have much more complicated emails, which doesn’t seemed to be in your example data, you can also use regular expressions, which actually only replaces the first occurrence of the search pattern, which the replace would replace also

    UPDATE user
SET email = REGEXP_REPLACE(email, '^GMSDK', 'GMSD') WHERE email LIKE 'GMSD%@law.com'

    Rows matched: 2  Changed: 2  Warnings: 0

    SELECT * FROM user
    email
    [email protected]
    [email protected]

    fiddle

    Login or Signup to reply.
  2. 0

    Your initial query seems… off:

    SELECT email FROM users WHERE user LKE ...

    Shouldn’t that be:

    SELECT email FROM users WHERE email LIKE ...

    Also, LIKE 'GMSD%@law.com' should probably be LIKE 'GMSDK%@law.com', to capture values that actually have a K in it.

    I would expect something simple, like using MySQL’s REPLACE() function should handle this:

    UPDATE
  users
SET
  email = REPLACE(email, 'GMSDK', 'GMSD')
WHERE
  email LIKE 'GMSDK%@law.com'

    Edit: In my experience, table names are plural, so users instead of user. If you are indeed using user, adjust as required.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search