Re: how do i do an ORDER BY with caseinsensitivity?

Holger Maertens wrote: > > Paul Roberts wrote: > > Can we use these to tell the engine that we want to consider the > > alphabet to be sequenced like this: "AaBbCcDdEeFfGgHhIi.....Zz" > > instead of like this: "ABCDEFGHI....Zabcdefghi....z" > > > > And if we could persuade the engine to accept this, would it not > > give us case-insensitive ORDER BY? > > Not really. For example, you would still have "Zzzzzz" < "zaaaaa" > (same with any other re-ordered alphabet). Your solution would work > if one could be sure that all names (I think the problem was about > names) start with upper and continue with lower case, but there are > people like "de Sade" and "McCarthy". Also, if all names did follow > that rule, you could just use the default order without any problem. > > I don't have a better solution, though, except the old "use an extra > column with the name all in upper case". OK, how about this: In addition to * select the result of passing the name to the UPPER() stored procedure that is floating around and ORDER BY 1 (or 12 or whatever). This would be good for the occassional query since it would not cost storage but it will be too expensive for frequent usage. In this case Holger is right: "use an extra column in upper (or lower) case". Art S. Kagel