Dan's workaround is very useful and Øyvind's suggestion is interesting
also. It's worth pointing out that the need for these workarounds should
decrease after we fix bug 533 (the re-enabling of the natonal character
datatypes). I hope to get to that bug in 10.2. The national character
datatypes will give us language-sensitive collation in indexes and sorts.
Cheers,
-Rick
[EMAIL PROTECTED] wrote:
Daniel John Debrunner wrote:
A post on the Cloudscape forum asked how to do ordering based upon the
Polish alphabet. I think there have been similar posts here.
http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=370&thread=96898&message=13759450&cat=19
I realised today there is a way with Derby, thanks to the new
functionality in 10.1, order by expressions (Thanks, Tomohito!).
I experimented with my idea and it basically works, though probably
could do with some refinements.
The trick is to write an user defined function that takes a string and
returns a value that is correct for ordering on. Say we have a specific
function POLISH_ORDER, then you can write.
SELECT ID, NAME FROM CUSTOMER ORDER BY POLISH_ORDER(name)
Building upon this:
Instead of requiring that the user creates functions like
POLISH_ORDER, FRENCH_ORDER, etc., how about having an out-of-the-box
LOCALE_ORDER function which takes the locale values as arguments, in
addition to the VARCHAR it orders on?
Something like
public static byte[] localeOrder(String loc1, String loc2, String
value)
{
Locale locale = getLocale(loc1, loc2);
return getBinaryCollationKey(value, locale);
}
private static Locale getLocale(String loc1, String loc2)
{
// Returned cached locale object or create new is none exists
}
Then one could write
SELECT ID, NAME FROM CUSTOMER ORDER BY LOCALE_ORDER('pl', 'PL', NAME)
