Hello,
Using MySQL, i'm selecting a list of songs in spanish that i would
like to sort. Here's a demo of the list of names that is returned by
the query:

¡Decirevilla!
Alhambra
123 pasitos
África
Arroz
Decir

The sorted list should look like this:

123 pasitos
África
Alhambra
Arroz
¡Decirevilla!
Decir

In other words, some special characters should be ignore (!, ?, ¡, ',
¿, etc...) when sorting and other characters should use the natural
equivalent for sorting (Á -> A... É -> E...)

After all of the research i've read, i'm not sure if there is a way to
achieve this using MySQL. I was thinking of ignoring certain
characters (¡, ?, ', !, ¿) and maybe replacing other characters with
the natural equivalent (Á -> A, É -> E, etc..), but i'm not sure if
it's the right path to go....

Does it make sense? Is this feasable in MySQL?


Thanks in advance for all your advice.
Marco

On 3/3/2011 10:50 PM, SM wrote:
What collation do you have for your column, and what collation are you
using on the connection?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 04-03-11 04:50, SM wrote:
CREATE TABLE `sp` (
`t` varchar(20) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT * from sp order by t;
+----------------+

CREATE TABLE `sp` (
`t` varchar(20) character set utf8 collate utf8_spanish2_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

+----------------+

CREATE TABLE `sp` (
`t` varchar(20) character set latin1 collate latin1_spanish_ci NOT
NULL default '',
PRIMARY KEY (`t`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

+----------------+

.....



--
Luuk

On Mar 4, 2:03*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:
that's exactly my point... no collation or charset will work on this
case... I mean, why would you want to sort a character like ¡ or ? or
¿... anyways, that's not the point...

After a day of researching and asking around, i believe that i'm
better off letting MySQL handle the sorting and not the PHP. For that,
i need to build an artificial column in my table that does sort
properly... a column called, say, sortable_title; then ORDER BY
sortable_title... When doing INSERTs and UPDATE's, I will use use a
normalize() function in PHP that produces a list with no punctuation,
all lower case, accents stripped, etc...

I believe at the end, that it's the best solution...

Thanks for all of your tips and guiding.

On 3/4/2011 7:50 PM, SM wrote:
If you look at Luuk's output, with the collation latin1_spanish_ci, it
is ignoring the ¡.

Your best solution is to use the correct tools.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

SM wrote:
I think it is the most pragmatic.

You want an order based on something other than the native tools provide.

Ergo the sorting scheme will have to be coded at some level by you.

In the case of a database which is write seldom read many, the time to
do that is during a write. If you can determine the sort rank at that
time, which you can.

Since you want a quick sort, it is optimal to use an SQL field to store
that rank.

In short your solution is optimal.



> Thanks for all of your tips and guiding.

On 3/4/2011 7:50 PM, SM wrote:
If you're insisting on adding another column for sorting, then update it
with a trigger. Don't depend on the application to do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 05-03-2011 15:10, Jerry Stuckle wrote:
When doing INSERTs and UPDATE's, I will use use a
But that will only be possible if the function he describes above
available is in mysql...

--
Luuk

On 3/5/2011 11:55 AM, Luuk wrote:
What does he want to do which can't be done in MySQL?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Luuk wrote:
application to do an insert or an update.

Since calculating what goes in the field will be part of what goes
before you insert it or update it.