db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: Language based ordering is possible in Derby
Date Thu, 03 Nov 2005 14:50:22 GMT
On Thursday 03 November 2005 03:59, Oyvind.Bakksjo@sun.com 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

Mime
View raw message