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

Replace text in SQL – Mysql

I have a field called EMAIL_ADDRESS. One of the records would be:

[email protected], [email protected], [email protected], [email protected], [email protected]

I wan to remove all yahoo addresses in my SELECT query to get:

[email protected], [email protected], [email protected]

If I use

REPLACE(SM.SCORECARD_EMAIL_ADDRESS, '[email protected],', '')

this works.

If I want to remove ALL yahoo email addresses this doesn’t work:

REPLACE(SM.SCORECARD_EMAIL_ADDRESS, '%@yahoo.com,', '')

because wildcards don’t seem to work as it’s looking for % in the string.

Tags:

2

Answers


  1. 0

    You should probably fix your table design and stop storing CSV lists of email addresses. Instead, get each email onto a separate record. As a short term fix, if you’re running MySQL 8+, you may use REGEXP_REPLACE():

    UPDATE yourTable
SET EMAIL_ADDRESS = REGEXP_REPLACE(
                        REGEXP_REPLACE(EMAIL_ADDRESS, '(, )?\S+@yahoo\.com,?', ','), '^,+|,+$', '')
WHERE EMAIL_ADDRESS LIKE '%@yahoo.com%';
    Login or Signup to reply.
  2. 0

    If you don’t need to udpate records but you want them only in the SELECT query you can use NOT LIKE operator

    SELECT * FROM your_table WHERE email NOT LIKE '%yahoo.com'

    So you get records that doesn’t match the like pattern

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

Back To Top
Search