CODEWITHSUNDEEP
mysqlregex
user4069489
user4069489

Reputation:

mysql remove points from the E-mail Addresses

I have few gmail addresses in my table

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

I need to replace addresses gmail that contain the symbol '.' to get:

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

my query does not work

UPDATE voters set email
replace(substring(email, 1, LOCATE('@', email) -1), '.', '')
WHERE email REGEXP '@googlemail.com|@gmail.com'

please help, thx!

Upvotes: 1

Views: 652

Answers (2)

Logan
Logan

Reputation: 1371

You can use something similar as the following query using substring_index & substring: 

UPDATE table1
SET email = CONCAT (
        replace(substring_index(email, '@', 1), '.', '')
        ,substring(email, instr(email, '@'))
        )
WHERE substring_index(email, '@', -1) IN (
        'googlemail.com'
        ,'gmail.com'
        );

SQLFiddle

Upvotes: 0

Barmar
Barmar

Reputation: 781726

You're extracting the substring before the @ and replacing the dots, but you're not adding back the substring after the @ after you do this:

UPDATE voters 
SET email = CONCAT(replace(substring(email, 1, LOCATE('@', email) -1), '.', ''),
                   SUBSTRING(email, LOCATE('@', email)))
WHERE email REGEXP '@googlemail.com|@gmail.com'

DEMO

Upvotes: 1

Related Questions