CODEWITHSUNDEEP
mysql
stUrb
stUrb

Reputation: 6842

Transform/sanitize wrongly formatted email adresses in table

I've got a table which holds email adresses. But over the years of use this table got messed up and some of the email adresses got saved in the following format.

Some Name <[email protected]>

Probably because of some user copy-pasting the email adres from an other application. In an effort to sanitize this column I'm searching for a single sql-query to perform this sanitizing.

I'm a little bit stuck on how to approach this problem on a pure MySQL way. That is, how to transform the above mentioned format (and only that format) to only the email adres between the < >.

Filtering those 'wrong' emails is fairly simple:

SELECT * FROM table WHERE email like '%<%>%'

But then....

UPDATE table 
SET email = ??? 
WHERE email like '%<%>%'

Upvotes: 0

Views: 98

Answers (2)

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2434

I modified @shadowsheep's answer so that it also sanitizes emails that have < but not >, and > but not <. I ran into that scenario (clients' databases not properly sanitized).

The following is an example via a select so you can see the result that you'll get, but you can easily adapt it as an update:

SELECT
    CASE 
        WHEN 
            instr(email,'<') > 0 AND instr(email,'>') > 0
        THEN
            REPLACE(
                REPLACE(
                    SUBSTRING(
                        email,
                        instr(email,'<')
                    ),'<',''
                ),'>',''
            )
        WHEN
            instr(email,'<') > 0 AND instr(email,'>') = 0
        THEN
            SUBSTRING(
                email,
                instr(email,'<') + 1,
                CHAR_LENGTH(email)
            )
        WHEN
            instr(email,'<') = 0 AND instr(email,'>') > 0
        THEN
            SUBSTRING(
                email,
                1,
                instr(email,'>') - 1
            )
        ELSE email
END AS email
FROM table 
WHERE email LIKE '%<%' OR email LIKE '%>%';

Upvotes: 0

shadowsheep
shadowsheep

Reputation: 15012

If I did understand your needs, that's your solution:

UPDATE table 
SET email = REPLACE(REPLACE(SUBSTRING(email,instr(email,'<')),'<',''),'>','')
WHERE email like '%<%>%'

or

UPDATE table 
SET email = REPLACE(SUBSTRING(email,instr(email,'<')+1),'>','')
WHERE email like '%<%>%'

Tried here.

Upvotes: 1

Related Questions