By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

case insensitive sorting & searching in oracle 10g

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
- 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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

P: n/a
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

Nov 23 '05 #6

P: n/a
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

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.