Jump to content

Archived

This topic is now archived and is closed to further replies.

blakekr

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

Recommended Posts

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 ...

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.