On Tue, 8 Jun 2004, Renato Cramer wrote: > Hello Andy, > > I don't known if this is possible without handle string, what I don't guess > recommended, because of performance and legibility of code. > > One suggestion will be store in column 'surname' (or other) the data already > in format of sort. > In other words, will be two columns in table, and, depending on approach, > the second column will can be disabled for the users. > > Example: > Name: Marco van Basten > Archive: Basten, Marco van > > I hope that helps.
Well, this was fixed in the end by this query: select substring_index(surname,' ',-1) as r from advisers order by r which produced the desired result. But we have since had complaints from individuals wanting their surnames sorted differently! People from Germany with surnames such as 'von Neumann' like to have this sorted with the V's and not the N's while people from the Netherlands with 'van den Berg' want it to be with the B's and not the V's. We are now redesigning the table to allow records to be displayed in a specific order chosen by the administrator, rather than trying to do this automatically by a SELECT statement. Thanks for your suggestion anyway. Andy > -----Mensagem original----- > De: andy thomas [mailto:[EMAIL PROTECTED] > Enviada em: terça-feira, 8 de junho de 2004 08:51 > Para: [EMAIL PROTECTED] > Assunto: 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 > # include <std-disclaimer.h> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
