Replace text in SQL-CodePudding

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.

CodePudding user response：

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%';

CodePudding user response：

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