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.

October 20, 2018

« TIL: SSH Key Permissions Must Be Exact! - How to authenticate via JWT in Postman »