Introduction

Removing whitespace from a MySQL column is a common cleanup task when imported or user-entered text contains inconsistent spacing. The correct SQL depends on whether you want to remove only leading and trailing whitespace or truly remove all whitespace characters inside the string as well.

Remove All Spaces With REPLACE

If you only care about literal space characters, REPLACE is the simplest approach:

UPDATE users
SET username = REPLACE(username, ' ', '');

This removes every plain space from the column, including spaces in the middle of the string.

Example:

• '"jane doe" becomes "janedoe"'
• '" admin " becomes "admin"'

Remove Leading and Trailing Whitespace Only

If the goal is only to trim the outside edges of the value, use TRIM instead:

UPDATE users
SET username = TRIM(username);

This preserves internal spaces while removing leading and trailing ones.

That distinction matters a lot. Full whitespace removal and trimming are not the same operation.

Remove Tabs and Newlines Too

If your data may contain tabs or line breaks, chain REPLACE calls:

1UPDATE users
2SET username = REPLACE(
3                  REPLACE(
4                    REPLACE(username, ' ', ''),
5                  '\t', ''),
6                '\n', '');

This is a practical approach on MySQL versions where more expressive regex replacement is not available or not desired.

MySQL 8.0: Use REGEXP_REPLACE

If you are on MySQL 8.0, REGEXP_REPLACE can remove all whitespace classes more cleanly:

UPDATE users
SET username = REGEXP_REPLACE(username, '[[:space:]]+', '');

This is usually the most compact answer when you want to remove:

• spaces
• tabs
• newlines
• repeated whitespace runs

The character class [[:space:]] is broader than a literal single-space replacement.

Preview Before Updating

Before updating the whole table, preview what will change:

1SELECT username,
2       REGEXP_REPLACE(username, '[[:space:]]+', '') AS cleaned
3FROM users
4LIMIT 10;

This is a good habit for data-cleaning operations because it lets you confirm that the transformation matches the business rule.

Update Only Rows That Need It

You can avoid touching already-clean rows:

UPDATE users
SET username = REGEXP_REPLACE(username, '[[:space:]]+', '')
WHERE username REGEXP '[[:space:]]';

That reduces unnecessary writes and makes the intent clearer.

Keep the Original Data if the Cleanup Is Risky

If you are not fully sure the transformation is correct, copy the original column into a backup column first or run the update inside a transaction where appropriate:

ALTER TABLE users ADD COLUMN username_original VARCHAR(255);
UPDATE users SET username_original = username;

That gives you a straightforward rollback path if the whitespace removal turns out to be too aggressive.

Common Pitfalls

The most common mistake is using TRIM when the real requirement is to remove whitespace everywhere. TRIM only affects the ends of the string.

Another issue is removing all spaces from data that actually needs internal spacing, such as full names or postal addresses. Be sure the target column is supposed to lose internal whitespace before running a mass update.

A third pitfall is forgetting about tabs and newlines. Replacing only ' ' does not remove every whitespace character users or imports may have introduced.

Finally, always test on a sample first and back up important data before a broad update. Data-cleaning operations are easy to write and easy to apply too aggressively.

Summary

• Use REPLACE(column, ' ', '') to remove literal spaces everywhere.
• Use TRIM(column) only for leading and trailing whitespace.
• Chain REPLACE calls or use REGEXP_REPLACE for tabs and newlines too.
• Preview the cleaned values before updating the full table.
• Make sure the business rule really calls for removing internal whitespace, not just trimming it.