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,
--
John English