I think he was saying that doing "ORDER BY UPPER(x)", where x is a column of type INT, did something strange:
> " for numberical columns I will end up sorting textually: values 1,2,10 will > be sorted as 1,10,2." On Sat, Aug 21, 2021 at 8:34 AM Rick Hillegas <rick.hille...@gmail.com> wrote: > > Some responses inline... > > On 8/21/21 8:03 AM, John English wrote: > > 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? > Unfortunately, you have to create a new database and copy your old data > into the new database. I would recommend creating a fresh database which > has the correct, case-insensitive collation. Then copy the old data into > the new database using the foreign views optional tool. See > https://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.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 > > > > 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"? > Or I don't understand your problem. I thought that you needed a string > like abc2def to sort before abc10def. Sort order should be correct for > numeric and date/time datatypes. It's just the character typed data > which sorts incorrectly. > > > >> 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, > >