Re: Case insensitive ORDER BY?
On Sun, 22 Aug 2021, 02:49 Rick Hillegas, wrote: > Hm. I don't think that UPPER operates on numeric data > No, I elided the conversion to char: upper(char(X)) or whatever. And now I understand what you meant. All I want is string data sorted case insensitively, and numeric data sorted numerically, so changing the collation will probably be OK. Thanks again. > >
Re: Case insensitive ORDER BY?
Hm. I don't think that UPPER operates on numeric data: ij> CONNECT 'jdbc:derby:memory:db;create=true'; ij> CREATE TABLE t(a int); 0 rows inserted/updated/deleted ij> INSERT INTO t VALUES (2), (10), (21); 3 rows inserted/updated/deleted ij> SELECT * FROM t ORDER BY UPPER(a); ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. On 8/21/21 2:45 PM, Bryan Pendleton wrote: 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 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,
Re: Case insensitive ORDER BY?
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 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, > >
Re: Case insensitive ORDER BY?
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,
Re: Case insensitive ORDER BY?
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
Re: Case insensitive ORDER BY?
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
