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 [email protected] and turn it to this [email protected]
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 `[email protected]` 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 '[email protected] ' email
union
select '[email protected]'
union
select '[email protected]'
) t
where email like '%@%..%'
Borja
- 1