You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2021/08/20 13:02:46 UTC
Case insensitive ORDER BY?
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
--
This email has been checked for viruses by AVG.
https://www.avg.com
Re: Case insensitive ORDER BY?
Posted by John English <jo...@gmail.com>.
On Sun, 22 Aug 2021, 02:49 Rick Hillegas, <ri...@gmail.com> 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?
Posted by Rick Hillegas <ri...@gmail.com>.
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 <ri...@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,
>>
Re: Case insensitive ORDER BY?
Posted by Bryan Pendleton <bp...@gmail.com>.
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 <ri...@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,
>
>
Re: Case insensitive ORDER BY?
Posted by Rick Hillegas <ri...@gmail.com>.
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?
Posted by John English <jo...@gmail.com>.
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?
Posted by Rick Hillegas <ri...@gmail.com>.
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
>