Re: Language based matching

[Kathey Marsden]

Rick Hillegas wrote: 3) The locale-sensitive meaning of <, =, and > affected the operation of all orderings of national strings, including sorts, indexes, unions, group-by's, like's, between's, and in's. At one point I was keen on re-enabling the national string types. Now I am leaning toward implementing the ANSI collation language. I think this is more powerful. In particular, it lets you support more than one language-sensitive ordering in the same database. You and your customer face a hard problem trying to migrate national strings from Cloudscape 5.1.60 into Derby 10.1.3 or 10.2. I'm at a loss how to do this in a way that preserves Cloudscape's performance. Thank you so much Rick for helping me understand this stuff. For now lets just assume this is just a small dataset and  set  performance aside I am interested to know 1)   When might Locale specific matching  be different  in the context  WHERE value LIKE '%<   >%'  (or whatever language we use)  besides the deprecated Norwegian 'aa' and when might this be useful?  Is it somehow related to bidirectional data like Hebrew and Arabic?  2)  Is there some easy java code that can be used to accomplish writing a LOCALE_MATCHES(pattern,value) function? For the other functionality  I have these equivalent functions to offer as a workaround (see http://wiki.apache.org/db-derby/LanguageBasedOrdering) ORDER BY -  Use ORDER BY _expression_ with  LOCALE_ORDER function implemented with Collator.getCollationKey()  <, =,  > , BETWEEN  -  Use LOCALE_COMPARE function implemented with Collator.compare() IN - Since this is an exact match, would the non-locale specific matching work ok here? GROUP-BY - No solution yet but GROUP BY _expression_ in progress will allow LOCALE_ORDER to be used. LIKE - ????????   Is there some easy Java regular _expression_ matching function  like String.matches(Collator collator, String pattern, String value)?     I can't find it.    The code in org.apache.derby.iapi.types.Like looks pretty involved, but perhaps that is what is needed.   I  just want to confirm before I go down that path and try to figure it out. Thanks Kathey P.S.  I once came very close to getting a cash register meant to interface to a gas pump working in a Deli  with a scale  until Mother Nature stepped in and raised the Russian River to the point that it swallowed the whole thing up, so I have been known to try too hard for a workaround.  If  trying to workaround Locale specific processing with Derby with FUNCTIONS is   a doomed enterprise,  I welcome that perspective as historically I sometimes don't know when to give up.