Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 blakekr

blakekr
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 06 September 2006 - 02:27 PM

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

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 06 September 2006 - 02:43 PM

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

select * from table order by replace(sort_field,'"', '')

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 blakekr

blakekr
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 06 September 2006 - 02:44 PM

THANK YOU. Greatly appreciated ...

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 06 September 2006 - 02:44 PM

Actually, you may be able to use TRIM:

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.


Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 September 2006 - 03:36 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users