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
