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/
