MySQL remove space before order by clause

Question

I have a table full of "title" that I want to order by title. Problem is a lot of them have a empty space before the title. I am thinking of writting a php script to fix it all (super easy) but I was curious how could I do:

SELECT * FROM products ORDER BY title

But at the same time trimming the title so it doesnt order on the empty space. All in the same query without changing the data. God I hope I make sense.

So I am really looking for a mysql solution to this. For what its worth I use Zend_Db so a solution using that would be even better but I can manage straight MySQL.

Woebegone

Answer 1

You can use the TRIM function:

SELECT TRIM(title) as title, field2, field3 FROM products ORDER BY TRIM(title)

That ought to do it!

Isia

Answer 2

I'll answer myself because the exact solution to my question is:

SELECT * FROM products ORDER BY TRIM(title)

I'll still accept codykrieger's answer because he made it so I found my solution.

Here is the Zend_Db answer:

$products->fetchAll($products->select()->order('TRIM(title) ASC'));

Woebegone

Answer 3

A pure SQL query would look like this:

UPDATE products SET title = TRIM( title )

Chiefly

Thanks but as I said ideally I do not change the data just trim for the order by clause. – Woebegone

If that's your use case that's fine. However, it's certainly not ideal, as you need to perform an additional operation on the table for every single query in order to get results in the correct order. If your table gets large, you'll see a noticeable performance decrease. – Chiefly

i want to say with indexes, you should watch out for sargable issues. dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html – Chip