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.
Hope this helps,
-Rick
On 2/2/23 12:35 AM, John English wrote:
I would like to be able to specify an ORDER BY which will give me the
same sort of ordering as produced by my file browser, where embedded
numbers in a string with the same prefix are ordered numerically.
For example: if I have strings 'x1','foo1','x2','foo10','foo5', a
normal text sort will give 'foo1','foo10','foo5','x1','x2' (that is,
'foo10' will come before 'foo5'). I would like to be able to produce
the order 'foo1','foo5','foo10','x1','x2' instead.
I could do this in Java by defining a custom comparator, and then
import this for use in Derby with CREATE FUNCTION but how would I then
use it in an ORDER BY clause to give me the desired sort order?
Thanks for any tips,
