noob: Getting MYSQL to ignore punctuation in alphabetical "ORDER BY"s?

[blakekr]

I can't believe I couldn't find this answer myself, but all I'm turning up are answers for C programs.

How can I tell MYSQL to ignore punctuation when doing its ORDER BY thang alphabetically? All my results in quotations are listed first. I want to preserve the quotations for user-readability, but I sure don't want to sort by them. Appreciate an ???y tips ...

[effigy]

The following will ignore double quotes. To my knowledge, you'll have to put together a custom function to get rid of other punctuation.

[code]
select * from table order by replace(sort_field,'"', '')
[/code]

[blakekr]

THANK YOU. Greatly appreciated ...

[effigy]

Actually, you may be able to use TRIM:

[quote]
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  bar  ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

This function is multi-byte safe.
[/quote]

[fenway]

Just be aware that this will prevent use of an index on this column -- if this is a heavy-duty table, and you're doing this a lot, you may simply want to store a "unquoted" version of the string in another column, put an index on that column, and use it instead for the sorting, but retrieve the unaltered value for the user.  Just a thought.  Also, TRIM() won't remove any internal characters.