db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: Language based ordering is possible in Derby
Date Thu, 03 Nov 2005 09:59:40 GMT
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)

-- 
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/

Mime
View raw message