case-insensitive sort

[Andrey Voronov]

How I can implement case-insensitive sort without use function-based index?

[andrewst]

Originally posted by Andrey Voronov

> How I can implement case-insensitive sort without use function-based
> index?

I guess your options are:

1) ORDER BY UPPER(colname)
and have a full table scan

2) Create another column UPPER_COLNAME and a BEFORE INSERT/UPDATE
trigger that sets :NEW.upper_colname = :NEW.colname;
create an index on UPPER_COLNAME;
then ORDER BY upper_colname

--
Posted via [url]http://dbforums.com[/url]

[Jim Kennedy]

I don't think that necessitates a full table scan. It is an order by which
is performed AFTER the result is selected. Try it where you get 1 row back
and do the order by. I would be very surprised if it did a full table scan.
eg
select * from emp where emp_no=3 order by upper(last_name)
(assuming emp_no is the primary key and last_name is a column in the table)
Jim

--
Replace part of the email address: [email]kennedy-down_with_spammers@attbi.com[/email]
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"andrewst" <member14183@dbforums.com> wrote in message
news:2381014.1042466888@dbforums.com...

>
> Originally posted by Andrey Voronov

> > How I can implement case-insensitive sort without use function-based
> > index?

> I guess your options are:
>
> 1) ORDER BY UPPER(colname)
> and have a full table scan
>
> 2) Create another column UPPER_COLNAME and a BEFORE INSERT/UPDATE
> trigger that sets :NEW.upper_colname = :NEW.colname;
> create an index on UPPER_COLNAME;
> then ORDER BY upper_colname
>
> --
> Posted via [url]http://dbforums.com[/url]

[David Fitzjarrell]

Using SQL Server comes to mind ...

Your other option is to convert the values to either all upper-case or
all lower-case and sort (order) on the converted column:

select ..., ..., ...
from ...
where ...
order by upper(column_name)

This, of course, is not as fast as having the associated
function-based index in place, but it does work.

You could, as an application modification, store the case-insensitive
values in another column, index that, then perform your order by
operations. Of course, that requires change control and an outage,
something you may be willing to do.

David Fitzjarrell


"Andrey Voronov" <avoronov@diasoft.ru> wrote in message news:<avtmo8$23jf$1@gavrilo.mtu.ru>...

> How I can implement case-insensitive sort without use function-based index?