ORDERY BY and quotes

[dylan38]

Hello,

Is there any way to get an ORDER BY to ignore quotation marks? For example, say I have a bunch of book titles:

Title 1
Title 2
"Title 3"
Title 4

Currently, using a simple ORDER BY, it will return:

"Title 3"
Title 1
Title 2
Title 4

How can I get it to return "properly" (or, rather, the way I want it to).

[xblue]

I think it would be best to remove the quotation marks to have your data consistent, e.g.
UPDATE table SET col_name=TRIM('"' FROM col_name) WHERE col_name LIKE '"%"'
(and strip them when new data is inserted, too).

[superwormy]

SELECT REPLACE(title, '"', '') AS title ORDER BY title

http://www.mysql.com/doc/en/String_functions.html

[dylan38]

The data is consistent, and using trim is not a valid option in this particular case.

[xblue]

if you cannot change the data for some reason, you could still use TRIM (or REPLACE) in your SELECT query as sorting criteria.