On 02/02/2023 17:13, Rick Hillegas wrote:
I can think of two solutions to your problem:

1) Write a custom Java collator and install it as the default sort order for all string data in your database. See the following sections in the Derby Developer's Guide: https://db.apache.org/derby/docs/10.16/devguide/cdevcollation.html and https://db.apache.org/derby/docs/10.16/devguide/tdevdvlpcustomcollation.html#tdevdvlpcustomcollation

2) The second approach, as you suggested, is to create a Derby function which recodes string types into some other type like VARBINARY. The function would need to divide the string into its alphabetic and numeric components and then reassemble them in a binary order which sorts the way you want. Your query would then look like:

   SELECT ... ORDER BY (recodingFunction(alphanumericColumn))

There are a lot of fiddly subtleties to both approaches. I can't give more advice because I haven't tackled this problem myself.

Thanks, Rick. I can't see how I would do method 2, as I want 'foo5' to come before 'foo10', but I also want 'x1' to come after both, and since the numbers aren't in the same place (or there might be more than one numeric part -- 'Section 5 para 10", for example) I don't see how to do this except of I have two values to compare.

I'll look at the DB and think about what method 1 might affect, since it would apply to the entire DB. Off the top of my head, I can't think of any problems, but the devil is in the detail.

It's a pity that there isn't a way to specify a collation order other than ASC/DESC in individual queries. Oh well.

Thanks again,
John English

Reply via email to