On Tue, 8 Jun 2004, Franz, Fa. PostDirekt MA wrote: > 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).
This is what I did in the end, to alwyas use the last word that's separated by a space. > 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. Well, we have run into this problem already since I 'fixed' the ordering! As there are people from all over the world using this database, we are now about to redesign the table to allow individual people to decide where they want their surname to appear in the listing. Thanks for your suggestions, Andy > -----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] > # include <std-disclaimer.h> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
