Trimming TAB Characters in MySQL
Here's how you use MySQL's TRIM() function for non-space characters.
So, I ran into a "funny" sorting issue this week. Turns out there was a <TAB>
in a product title, causing that title to be sorted first. (Quite logical if you look at the ASCII table.)
I ran this query to trim the tab characters from the field...
UPDATE products
SET title = TRIM( title );
... but it turns out the function only handles spaces by default.
Here's the magic combo I settled on. (Ok, ok, SO told me.)
UPDATE products
SET title = TRIM( CHAR(9) FROM title );
While I was at it, I checked out the MySQL docs for the Trim() function, and it's actually pretty powerful. Take a gander at this:
UPDATE products
SET title = TRIM( LEADING CHAR(9) FROM title );
UPDATE pages
SET url = TRIM( TRAILING '/' FROM url );
UPDATE dummy
SET myString = TRIM( BOTH 'x' FROM myString );
This is a very handy function to keep in mind, given that it can trim much more than just whitespace. Coldfusion's trim() function only trims spaces, and Javascript's trim() function only trims whitespace.