User login

Removing double spaces in usernames directly in the SQL

A client using the Bulk invite module (by Agaric's David Valdez) had invited hundreds of users with slightly malformed usernames— two spaces between first and last name when there should only be one.

Note that you need to do the change both in the users_field_data table and the users_field_revision table— and you need to clear caches when you're done to see the change on user edit pages, which is where it matters.

drush sqlc
UPDATE users_field_data SET name = REPLACE(name,'  ',' ');

Check for the response! For example, the first time i ran this it returned:

ERROR 1062 (23000): Duplicate entry 'John Doe-en' for key 'user__name'

This was because the same name was there twice (once with a single space and once with a double space) associated with different e-mail addresses (but almost certainly the same person). Rather than make the decision of which e-mail address should be used for this user, it was easiest to rename the existing username for the second address:

UPDATE users_field_data SET name = REPLACE(name,'John Doe','JohnDoe');

And then the original command could complete, like this:

mysql> UPDATE users_field_data SET name = REPLACE(name,'  ',' ');
Query OK, 243 rows affected (0.02 sec)
Rows matched: 497  Changed: 243  Warnings: 0

And then the users_field_revision table, in our case still with the extra step:

UPDATE users_field_revision SET name = REPLACE(name,'John Doe','JohnDoe');
Query OK, 1 row affected (0.01 sec)
Rows matched: 798  Changed: 1  Warnings: 0

mysql> UPDATE users_field_revision SET name = REPLACE(name,'  ',' ');
Query OK, 250 rows affected (0.01 sec)
Rows matched: 798  Changed: 250  Warnings: 0

Then exit MySQL/MariaDB/whathaveyou and clear caches:

../vendor/bin/drush cr
Searched words: 
SQL search for double space MySQL find and replace doublespace in database field


Post new comment

The content of this field is kept private and will not be shown publicly.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You can use Markdown syntax to format and style the text. Also see Markdown Extra for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <img> <blockquote> <small> <h2> <h3> <h4> <h5> <h6> <sub> <sup> <p> <br> <strike> <table> <tr> <td> <thead> <th> <tbody> <tt> <output>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.