case insensitive searching

case insensitive searching

Postby csadler » Fri 12 Sep 2008 14:25


Oracle 10g, C# 3.5

I'm trying to set case insensitive searches base on what I have found on the web and tested in toad:

_command = new OracleCommand("", _connection);
_command.CommandText = "alter session set nls_comp = ANSI";
_command.CommandText = "alter session set nls_sort = BINARY_CI";

This does not seem to work.

Any pointers would really be appreciated!


Posts: 2
Joined: Fri 12 Sep 2008 14:18

Postby Shalex » Mon 15 Sep 2008 11:02

Please refer to the SQL documentation. We don't support such functionality.
Devart Team
Posts: 7888
Joined: Thu 14 Aug 2008 12:44

is this still the case in v5.70?

Postby slaxman » Fri 05 Aug 2011 19:59

we would like to change the NLS_COMP and NLS_SORT settings at session level? do you have an API support in v5.70? if not, can we prepend the setting change scripts as part of the same connection simulating a session level change?
Posts: 47
Joined: Wed 16 Sep 2009 20:09
Location: United States

Postby Shalex » Mon 08 Aug 2011 16:07

We do not provide API for changing NLS_COMP and NLS_SORT. Try setting these initial parameters at the session level via OracleCommand. Be aware that Direct mode does not support NLS conversion on client side.
Devart Team
Posts: 7888
Joined: Thu 14 Aug 2008 12:44

Postby gustavp » Tue 09 Aug 2011 11:44

Wouldn't it be easier to create a functional index in your DB (not even required, but faster for searching), and use the UPPER() function in you query?

some pseudo code to explain:
string somestring = "somestring";
string query = "select * from sometable where upper(somecolumn) = :bind";
Bindings bind = db.bind(":bind",somestring.ToUpper());
db.prepare(query, bind);
ResultSet restult =;
Posts: 10
Joined: Wed 13 Jul 2011 08:44

Return to dotConnect for Oracle