How do I update an email containing a double dot in its domain using raw mysql query?
So I have these emails having this format sample_email@gmail..com and turn it to this sample_email@gmail.com
Asked
Active
Viewed 819 times
0
Lorence
- 25
- 1
- 9
-
@t.niese does that work in SQL? – jpaugh Jul 27 '17 at 16:45
-
@jpaugh sorry, somehow mixed two questions. – t.niese Jul 27 '17 at 16:45
-
@t.niese NP. I *wish* you could do that in SQL! I think I found a SQL duplicate. – jpaugh Jul 27 '17 at 16:46
-
Possible duplicate of [How to replace a string in a SQL Server Table Column](https://stackoverflow.com/questions/814548/how-to-replace-a-string-in-a-sql-server-table-column) – jpaugh Jul 27 '17 at 16:46
2 Answers
0
replace should do the trick:
UPDATE mytable
SET email = REPLACE(email, '..', '.')
WHERE email LIKE '%..%'
Mureinik
- 297,002
- 52
- 306
- 350
-
You'll probably want to make sure there aren't legitimate occurrences of `..` in the database first. Something like `chris..something@gmail.com` may be technically valid. – ceejayoz Jul 27 '17 at 16:48
-
@ceejayoz is there a way I can only target the double dot in the domain name? – Lorence Jul 27 '17 at 17:06
-
@Lorence Well, first, I'd prevent this sort of invalid stuff from getting to the database. After that, you can clean things up by using MySQL's regex capabilities. (Or, do a `SELECT * FROM mytable WHERE email LIKE '%..%'` - if there aren't any *legitimate* uses of `..` you can just run this update query). – ceejayoz Jul 27 '17 at 17:06
0
You just need to use '%@%..%' as a like condition to extract all emails that have double dot on the domain. Then split the text by @, replace in the domain and concatenate the text.
select email
,concat(left(email,instr(email,'@')),replace(right(email,instr(reverse(email),'@')-1),'..','.')) new_email
from
(
select 'sample_email@gmail..com ' email
union
select 'sample..email@..foo.com'
union
select 'other..sample..mail..@foo..com..org'
) t
where email like '%@%..%'
Borja
- 1