"case-insensitive" database 1

[vr76413]

Hello all

how do we make the data inside the objects like data in the tables be
case-insensitive ?....
Because i will be doing some sorts and i want all the data NOT to be sorted
on the case-sensitive thing ...


For eg:

SQL> insert into ram1 values ('john');
1 row created.
SQL> insert into ram1 values ('smith');
1 row created.
SQL> insert into ram1 values ('kevin');
1 row created.
SQL> insert into ram1 values ('SMITH');
1 row created.
SQL> SELECT * FROM RAM1 order by name;


So obviuosly, SMITH is different from smith...

How do i make changes to the database either at creation time or after inorder to avoid this problem and let oracle recognize SMITH and smith as same. and look like this when i issue the same above command :

SQL> SELECT * FROM RAM1 order by name;

NAME
----------
john
kevin
smith
SMITH

 

[carp]

select *
FROM ram1
ORDER BY UPPER(name);

 

[karluk]

Metalink suggests altering your session's NLS_SORT parameter to LATIN to get case insensitive sorts.

alter session set nls_sort = LATIN;

If you use Carp's suggestion and order by upper(name), you should look at creating a function based index to support this ordering.