Hi,
it is not possible to handle all cases proper.
You can just handle all cases you know with the REPLACE-function,
so you simply delete the prefixes in the WHERE-clause.
But that only works for all prefixes you know.
If you do like
ORDER BY REPLACE(REPLACE(surname,'du',''),'de','')
you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but
'de la Tour' will still apear at the wrong place.
I don't think you can be sure to remove all prefixes like this, because
you can't be sure to know all of them.
A different trick would be to say allways take the last 'word' in the surname,
which is much more efficent, but will unfortunally not work with double names like
'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to
show the principle).
So, there is not lot the world can learn from germany, but we treat all
these prefixes like they belong ti the name, which means 'von Hohenzollern'
is correctly ordered among the the v's and not the h's.
prosit
Klaus
-----Ursprüngliche Nachricht-----
Von: andy thomas [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 8. Juni 2004 13:51
An: [EMAIL PROTECTED]
Betreff: ORDER BY problem
In a table called 'advisers' I have a column called 'surname' which contains the
surnames of a number of people. Using a query like: 'select * from advisers order by
surname' lists the people in the correct order but some people have surnames like 'du
Sautoy' and 'van den Berg' and these are listed in the order of the first character
that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D'
rather than 'S', etc.
Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a
sort string, so that 'du' and 'van den' in the example above are effectively ignored?
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
