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 );