Sometimes a string isn't processed/trimmed properly and ends up in your database with leading or trailing whitespaces. This might not appear as a problem until you try to select the column. Luckily: A pain to debug, but pretty easy to fix.
Find database entries with trailing whitespaces #
Finding entries that have one or multiple trailing whitespaces is simple. You can run a LIKE query:
SELECT * FROM table WHERE `column` LIKE '% '
If you need to check multiple columns you can, of course, use an OR to expand the selection.
If you need to know if your table contains entries with trailing whitespaces you can run the same query with count:
SELECT count(*) FROM table WHERE `column` LIKE '% '
How to remove leading or trailing whitespaces from entries in MySQL #
Removal is almost as easy as identifying. You can use a MySQL built-in function to overwrite the result:
UPDATE table SET `column` = TRIM(`column`) WHERE `column` LIKE '% ';
As usual, if you aren't confident in using SQL make a backup of your database before starting and potentially destructive operations.
🙏🙏🙏
Since you've made it this far, sharing this article on your favorite social media network would be highly appreciated 💖! For feedback, please ping me on Twitter.
Published