On Thursday 03 November 2005 03:59, [EMAIL PROTECTED] wrote:
Is it just me, or didn't someone just post that UDRs were not available in
Derby, but that the code was still there in Cloudscape?
According to the Derby site at Apache, Derby supports UNICODE.
(Really a small little blurb.)
So in theory you should have the ability to do a sort order based on
the UNICODE character value.
But if you want a sort order, you may want to embed it in to the engine rather
as a slap on UDR. That is to say, that when you prepare a result set, you
could then check and sort based on how the Locale variable is set.
In theory, you'd want to create a system table that contained a
Locale_ID : INT,
Locale_name: CHAR(20),
UTF-8 : INT,
sort_order : INT,
Or something like that. This way you could have 'A' and 'a' have the same sort
order.
But hey, what do I know?
You could always pop over to IBM and review a bunch of white papers, redbooks,
etc on internationalization techniques.
-G
> 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&thre
> >ad=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)
--
Michael Segel
Principal
MSCC
