Remove unwanted whitespace from the column – MySQL

There is always the possibility that the users may not enter the values as we expected and the data is being 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 have 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
  3. Conclusion

 


 

1. Methods

  • TRIM() – This removes extra whitespace or character either from both side or 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

You can use 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 left side of the username column.
UPDATE users set username=TRIM(LEADING '-' FROM username)
  • Removes the '-' character from right side of the username column.
UPDATE users set username=TRIM(TRAILING '-' FROM username)
  • Removes the whitespace from left side of the name column
UPDATE users SET name = LTRIM(name)
  • Removes the whitespace from right side of the name column
UPDATE users SET name = RTRIM(name)

 

3. Conclusion

In this tutorial, I showed how you can remove the extra whitespace from your existing records using MySQL string functions.

With this methods, you can also eliminate other unwanted characters other than whitespace.

Spread the love

Related Post

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *