[Db-derby Wiki] Update of "LanguageBasedOrdering" by KatheyMarsden

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by KatheyMarsden:
http://wiki.apache.org/db-derby/LanguageBasedOrdering

------------------------------------------------------------------------------
  
  == The Start of a Solution ==
  
+ In order to solve the problem of language based ordering we will define Derby functions which use the built in Java [http://java.sun.com/j2se/1.5.0/docs/api/java/text/Collator.html java.text.Collator] class.  These can be used in an SQL Statement for language based ordering.  One issue with this is that no indexes are used for functions so there are performance implications with large data sets. Below is a summary of the operations and a possible replacement strategy:
+ 
+ ||'''SQL OPERATION'''||'''Replacement Strategy'''||
+ || ORDER BY ||  Use ORDER BY expression functionality with LOCALE_ORDER function based on Collator.getCollationKey(String value)||
+ || <, >,  BETWEEN|| Use LOCALE_COMPARE function based on Collator.compare(String source, String target)||
+ || IN, = || It is not clear if IN or = are affected, since these are an exact match is the default Derby processing ok? ||
+ || GROUP BY ||  GROUP BY expression is not available in Derby so there is no current replacement||
+ || LIKE || There is not a built in java matching function to use as a replacement for String matching. A LIKE equivalent should be possible but would be complex ||
+ 
+ 
- The example is not a complete solution but a place to start.
+ Below is an example for ORDER BY. It is not a complete solution but a place to start.
- Please fix up the example here on the Wiki as you make it better.
+ Please fix up the example here on the Wiki as you make it better and implement the other functions.
  
  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 function LOCALE_ORDER that takes language, country and value, then you can write: