db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Language based ordering is possible in Derby
Date Thu, 03 Nov 2005 14:42:45 GMT
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

Oyvind.Bakksjo@Sun.COM 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)
>


Mime
View raw message