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


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

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
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: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
If you need to sort the embedded numbers too, then you have to supply
a custom collator. See
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,

Reply via email to