On 20/08/2021 20:13, Rick Hillegas wrote:
You could solve this problem with a custom character collation. See https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html
Great!
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
I need to think a bit about whether I ever need case-sensitivity. I suspect not, but I'll need to go through the tables, and if I can't find any problems, this sounds like it might be the best solution.
Assuming this is a viable solution, is there a way to convert a live database from case-sensitive to case-insensitive (from collation=TERRITORY_BASED:TERTIARY to collation=TERRITORY_BASED:PRIMARY, if I understand correctly), which I assume will involve rebuilding all the indexes?
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
OK, this bit I didn't understand. Sometimes I want to sort on columns of numbers, sometimes dates, sometimes strings. Is that what you mean by needing to "sort the embedded numbers"?
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.
OK, will do. Many thanks, -- John English -- This email has been checked for viruses by AVG. https://www.avg.com
