CODEWITHSUNDEEP
mysqlsql
Karem
Karem

Reputation: 18103

User emails with whitespaces, fix with MySQL?

I found out that within my 65.000 users, there's many who have whitespaces in their email like this:

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

This could easily be solved by php trim() function, and I would get the clean email.

It isnt possible to enter a email at the input like this anymore, but it was and therefore I have emails like this in the first 20k users.

I wonder if I could do a smart UPDATE MySQL query, that cleans (trims the emails) in one run or if i have to use some php, where i grab all the users and go through them, trim() and update the row for each 65k users?

Upvotes: 1

Views: 170

Answers (2)

Kim Kling
Kim Kling

Reputation: 751

Use the trim() function in PHP and update all entries in your database. Be aware of PHP execution time, you may have to split up your updates in different executions.

Upvotes: -3

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174369

You can use TRIM in MySql as well:

update users set email = TRIM(email);

Upvotes: 4

Related Questions