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.
Contents
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
namecolumn.
UPDATE users SET name = TRIM(name)
- Removes the
'1'character from both of the ends of thenamecolumn.
UPDATE users set name=TRIM(BOTH '1' FROM name)
- Removes the
'-'character from the left side of theusernamecolumn.
UPDATE users set username=TRIM(LEADING '-' FROM username)
- Removes the
'-'character from the right side of theusernamecolumn.
UPDATE users set username=TRIM(TRAILING '-' FROM username)
- Removes the whitespace from the left side of the
namecolumn
UPDATE users SET name = LTRIM(name)
- Removes the whitespace from the right side of the
namecolumn
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.