Remove unwanted whitespace from the column – MySQL

There is always the possibility that the users may not enter the values as you expected and the data is saved on the Database table. E.g. unwanted whitespace or characters with the value.

You will see the issue when you check for duplicate records or sort the list.

MySQL has some string functions that you can use to eliminate the extra space or characters from the field.

Remove unwanted whitespace from the column - MySQL


Contents

  1. Methods
  2. Update Column
  3. Conclusion

1. Methods

  • TRIM() – This removes extra whitespace or character either from both sides or a specific side of the value.

Syntax –

TRIM( column-name )

OR

TRIM([LEADING|TRAILING|BOTH ] [ remove value ] FROM [field-name or string] )

Example

TRIM( name )

OR

TRIM(BOTH '' FROM name )
  • LTRIM() – This removes white space from the left side of the value.

Syntax –

LTRIM( column-name )
  • RTRIM() – This removes white space from the right side of the value.

Syntax –

RTRIM( column-name )

Example

RTRIM( name )

2. Update Column

You can use the following queries to update your existing records if the field contains unwanted spaces or characters.

  • Removes the whitespace from both of the ends of the name column.
UPDATE users SET name = TRIM(name)
  • Removes the '1' character from both of the ends of the name column.
UPDATE users set name=TRIM(BOTH '1' FROM name)
  • Removes the '-' character from the left side of the username column.
UPDATE users set username=TRIM(LEADING '-' FROM username)
  • Removes the '-' character from the right side of the username column.
UPDATE users set username=TRIM(TRAILING '-' FROM username)
  • Removes the whitespace from the left side of the name column
UPDATE users SET name = LTRIM(name)
  • Removes the whitespace from the right side of the name column
UPDATE users SET name = RTRIM(name)

3. Conclusion

Using the above-specified functions you can remove leading, trailing, or both leading and trailing whitespace from a specified column.

Choose a function according to your requirement.

If you found this tutorial helpful then don't forget to share.

Leave a Comment