case insensitive sorting & searching in oracle 10g

[David Garamond]

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

--
dave
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[David Garamond]

- not transparent
- can't automatically make all values fed to SELECT case-converted
- not transparent

Pierre-Frédéric Caillaud wrote:

create a functional index on lower case value of your column.
ORDER BY lower case value of your column.

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting
& searching? is there interest in seeing this in postgres?

--
dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tommi Maekitalo]

Hi,

I don't like the solution. "Select ... order by ..." should be self-contained
and not dependant of some settings. Case-insensitive sort should be specified
in the order-by-clause like "select ... order by lower(a)".
Tommi

Am Donnerstag, 5. August 2004 11:04 schrieb David Garamond:

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

On Thu, 5 Aug 2004, David Garamond wrote:

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either
set the variable immediately before sending queries that use it (breaking
the transparency) or must be willing to deal with the fact that it might
not be what you expect. For the second, I don't see how this really does
much that the standard spec collation stuff can't do better and I'd think
that'd be a much better route to go.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[David Garamond]

Stephan Szabo wrote:

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either
set the variable immediately before sending queries that use it (breaking
the transparency) or must be willing to deal with the fact that it might
not be what you expect. For the second, I don't see how this really does
much that the standard spec collation stuff can't do better and I'd think
that'd be a much better route to go.

Could you point me where in the archives can I read more? I'm having a
bit of trouble finding discussion on this. Thanks.

--
dave

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephan Szabo]

On Thu, 5 Aug 2004, David Garamond wrote:

Stephan Szabo wrote:

in oracle 10g, you can issue:

ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;

do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?

IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either
set the variable immediately before sending queries that use it (breaking
the transparency) or must be willing to deal with the fact that it might
not be what you expect. For the second, I don't see how this really does
much that the standard spec collation stuff can't do better and I'd think
that'd be a much better route to go.

Could you point me where in the archives can I read more? I'm having a
bit of trouble finding discussion on this. Thanks.

I didn't spend too much time looking, but there are a few that look like
they'll touch upon related issues:

http://archives.postgresql.org/pgsql...1/msg01299.php
http://archives.postgresql.org/pgsql...1/msg00610.php
http://archives.postgresql.org/pgsql...1/msg00515.php

And a message where I pulled some text out of the SQL92 draft:
http://archives.postgresql.org/pgsql...8/msg00620.php

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Pierre-Frédéric Caillaud]

IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either

And what if you use a connection sharing/pooling software ? What happens
with the session vars ?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org