You could solve this problem with a custom character collation. See
https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html
If you don't need to sort the embedded numbers, then the simplest
solution is to create a database which uses a case-insensitive sort
order. See
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcollation.html
If you need to sort the embedded numbers too, then you have to supply a
custom collator. See
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcustomcollation.html
It's hard to imagine that you are the first person who needs the sort
order you have described. Maybe a little googling will discover that
someone has open-sourced a collator which does the right thing. If you
can't find one but you end up writing your own, please consider
open-sourcing it.
Hope this helps,
-Rick
On 8/20/21 6:02 AM, John English wrote:
Is there any way to ORDER BY case-insensitively if you don't know the
column type?
I have a method in a webapp which displays tables with clickable
column headings which sort by the clicked-on column. I give it a
table/view name and a list of column names, and it does the rest. The
method is completely general and knows nothing about what it is
displaying.
My problem is that I want to sort case insensitively. I can of course
modify the method to generate ORDER BY UPPER(x) instead of ORDER BY x,
which will work for text columns, but for numberical columns I will
end up sorting textually: values 1,2,10 will be sorted as 1,10,2.
Any ideas?
--
John English
