db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tom_ <tom12...@web.de>
Subject Re: ORDER BY and greek characters
Date Mon, 01 Dec 2008 16:27:38 GMT

Hi Rick,

thank you for the suggestions, I have to examine them and will tell you if
there are further problems.

Tom


Rick Hillegas-2 wrote:
> 
> tom_ wrote:
>> I'm using ORDER BY and a database with collation territory based de_DE.
>> Strings starting with greek characters are at the end of the resultset.
>> Users would expect them near the respective latin character, e.g. greek
>> "alpha" near "a". Is there a possiblity to sort in this way?
>>   
>>   
> Hi Tom,
> 
> Here a couple suggestions:
> 
> 1) You can write your own public static method which takes a String 
> argument and returns a byte array, where the byte array sorts in the 
> order that you want--note that Derby sorts byte arrays 
> lexicographically, just like Strings. The existing support for 
> CollationKey should help you write this method. Your method's signature 
> would look like this:
> 
>   public static byte[] myOrder( String raw ) throws SQLException { ... }
> 
> Then you would register your method as a Derby function:
> 
> create function myOrder
> (
>     raw varchar( 100 )
> )
> returns varchar( 1000 ) for bit data
> language java
> parameter style java
> no sql
> external name 'MyClass.myOrder'
> 
> Finally, you would use this function in queries like this:
> 
> select * from t order by myOrder( a )
> 
> + The good thing about this solution is that it should work today on 10.4.
> - The downside is that you don't have indexing support for these queries.
> 
> 
> 2) You could change Derby so that it supports user-defined Locales with 
> custom collations. I haven't looked into the details here, but this 
> seems like a smallish task--gated by a community discussion about how to 
> declare these user-created Locales.
> 
> + This would be generally useful.
> + Indexes would work for you and give your queries better performance.
> - This solution wouldn't hit production until Derby 10.5 at the earliest.
> 
> 
> 3) You could build on solution (1) and use generated columns. As in (1), 
> you would write the myOrder function. Your would register this method as 
> a deterministic function:
> 
> create function myOrder
> (
>     raw varchar( 100 )
> )
> returns varchar( 1000 ) for bit data
> language java
> deterministic
> parameter style java
> no sql
> external name 'z.myOrder'
> 
> Then you would declare and index your table like this:
> 
> create table t( a varchar( 100 ), b generated always as ( myOrder( a ) ) )
> create index t_idx on t( b )
> 
> Finally, you would query your table like so:
> 
> select a from t order by b
> 
> + This would give you indexing support as in (2).
> - Generated columns won't be available until the next feature release,
> 10.5
> 
> 
> Let me know if you have more questions about these solutions.
> 
> 
> Hope this helps,
> -Rick
> 
> 
> 
> 
> 
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/ORDER-BY-and-greek-characters-tp20748193p20774503.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message