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