Home » SQL & PL/SQL » SQL & PL/SQL » case insensitive in data (win server 2003, oracle 9i)
case insensitive in data [message #342924] Mon, 25 August 2008 09:57 Go to next message
tsuria
Messages: 8
Registered: August 2008
Junior Member
Hello all.

I want my data in my database to be case insensitive.
not my tables names or columns names.

I saw some solutions that add commands to the query. i don't want this.
I want that i will write a regular query and i retrieve the full data.

Exmaple:
select * from names where firsName = aaa

Output:
firstName
----------
aaa
AaA
aAa
AAa
aaA


Someone knows how to do this?
It is possible at all?

Thanks a lot,
Tsuria.
Re: case insensitive in data [message #342927 is a reply to message #342924] Mon, 25 August 2008 10:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please search before posting.

http://www.orafaq.com/forum/t/73009/0/
Re: case insensitive in data [message #342952 is a reply to message #342927] Mon, 25 August 2008 11:08 Go to previous messageGo to next message
tsuria
Messages: 8
Registered: August 2008
Junior Member
i was searched, but i didn't found it.

Thank you,
Tsuria.
Re: case insensitive in data [message #343074 is a reply to message #342924] Tue, 26 August 2008 03:58 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

Please do this.This will help you


select * from (select lower(names) from names where lower(names)='aaa');

Regards
Re: case insensitive in data [message #343076 is a reply to message #342924] Tue, 26 August 2008 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I saw some solutions that add commands to the query. i don't want this.
I want that i will write a regular query and i retrieve the full data.


Can't (to the best of my knowledge) be done.
You'll need to add before update and before insert triggers to your tables to convert the data into a specific case, and then query using the same case.
Re: case insensitive in data [message #344736 is a reply to message #343076] Mon, 01 September 2008 02:28 Go to previous messageGo to next message
tsuria
Messages: 8
Registered: August 2008
Junior Member
OK, it seems that i have to add trigers.

thank you all,
Tsuria.
Re: case insensitive in data [message #344908 is a reply to message #344736] Mon, 01 September 2008 14:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Did you click on the link provided by Mahesh Rajendran and read the post in that link by Littlefoot? You can use that method to get the various mixed case versions. You can also set sqlcase upper prior to inserting to insert in all the same case instead of using a trigger. I have demonstrated both methods below. Neither method requires that you modify your select statement, but you will need to put the prior alter session or set commands in your login.sql or some such thing. Another case insensitive method is to use a context index with a contains query. I have also demonstrated that.

SCOTT@orcl_11g> CREATE TABLE names (firstname  VARCHAR2 (9))
  2  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO names VALUES ('aaa')
  3  INTO names VALUES ('AaA')
  4  INTO names VALUES ('aAa')
  5  INTO names VALUES ('AAa')
  6  INTO names VALUES ('aaA')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> ALTER SESSION SET NLS_COMP = ANSI
  2  /

Session altered.

SCOTT@orcl_11g> ALTER SESSION SET NLS_SORT = GENERIC_BASELETTER
  2  /

Session altered.

SCOTT@orcl_11g> SELECT * FROM names WHERE firstname = 'aaa'
  2  /

FIRSTNAME
---------
aaa
AaA
aAa
AAa
aaA


SCOTT@orcl_11g> TRUNCATE TABLE names
  2  /

Table truncated.

SCOTT@orcl_11g> SET SQLCASE UPPER
SCOTT@orcl_11g> INSERT ALL
  2  INTO names VALUES ('aaa')
  3  INTO names VALUES ('AaA')
  4  INTO names VALUES ('aAa')
  5  INTO names VALUES ('AAa')
  6  INTO names VALUES ('aaA')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> SELECT * FROM names WHERE firstname = 'aaa'
  2  /

FIRSTNAME
---------
AAA
AAA
AAA
AAA
AAA

SCOTT@orcl_11g> 


SCOTT@orcl_11g> CREATE TABLE names (firstname  VARCHAR2 (9))
  2  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO names VALUES ('aaa')
  3  INTO names VALUES ('AaA')
  4  INTO names VALUES ('aAa')
  5  INTO names VALUES ('AAa')
  6  INTO names VALUES ('aaA')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> CREATE INDEX names_idx ON names (firstname)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT * FROM names WHERE CONTAINS (firstname, 'aaa') >0
  2  /

FIRSTNAME
---------
aaa
AaA
aAa
AAa
aaA

SCOTT@orcl_11g> 


[Updated on: Mon, 01 September 2008 14:51]

Report message to a moderator

Re: case insensitive in data [message #344969 is a reply to message #344908] Tue, 02 September 2008 01:02 Go to previous message
tsuria
Messages: 8
Registered: August 2008
Junior Member
Thanks Barbara.
This is very helpful.
Tsuria.
Previous Topic: procedure with refcursor
Next Topic: syntax to see the package code
Goto Forum:
  


Current Time: Fri Dec 09 19:17:17 CST 2016

Total time taken to generate the page: 0.22749 seconds