Home » SQL & PL/SQL » SQL & PL/SQL » Problem to compare two elements
Problem to compare two elements [message #198262] Mon, 16 October 2006 06:01 Go to next message
samadou
Messages: 2
Registered: October 2006
Junior Member
Hi all,
I tried to compare two elements in my database:
select * from table where upper(col1) ='CARRE';
it does return columns contain 'carré' because of the "é". Can somebody help me. Is there a parameter to set in oracle so that my query return what I want?
Re: Problem to compare two elements [message #198267 is a reply to message #198262] Mon, 16 October 2006 06:19 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
you could use the replace option..but if you have more special characters you can alter the data
Re: Problem to compare two elements [message #198271 is a reply to message #198267] Mon, 16 October 2006 06:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look here for an possible solution.
Re: Problem to compare two elements [message #198457 is a reply to message #198262] Tue, 17 October 2006 04:33 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,
I took the value "carré" in my column and when i tried the query
select * from table where upper(col1) ='CARRE';
I didn't got the value "carré".
Re: Problem to compare two elements [message #198460 is a reply to message #198262] Tue, 17 October 2006 04:42 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Just use this site:

And go there, it is the solution you need to know about:

10gR2 new feature: Case Insensitive Sorts & Compares

http://www.orafaq.com/node/999

Most of it works on V9 too.
Re: Problem to compare two elements [message #198464 is a reply to message #198457] Tue, 17 October 2006 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm obviously missing something here.
Why don't you simply correct your query to search for the text string that's in the column:

SQL> create table temp_chr_set (col_1  varchar2(100));

Table created.

SQL> insert into temp_chr_set (col_1) values ('AAÉ');

1 row created.

SQL> select * from temp_chr_set where col_1 = 'AAÉ';

COL_1
---------------------------------------------------------
AAÉ

or if your keyboard won't do those chrs, 

SQL> select * from temp_chr_set where col_1 = 'AA'||chr(144);

COL_1
-------------------------------------------------------------
AAÉ
Re: Problem to compare two elements [message #198473 is a reply to message #198464] Tue, 17 October 2006 05:18 Go to previous message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
If the goal is to find is to find ANY text like 'CARRE' ('carré', 'caRRE'...), you could do this:

ALTER SESSION SET NLS_COMP='ANSI';
ALTER SESSION SET NLS_SORT='GENERIC_BASELETTER';

and then, just do:

select * from table where col1 ='CARRE';
(This works with 9i and 10g)

but
select * from table where col1 like 'CA%';
WON'T WORK.

It will work for you. Anyway, the use of upper(col1) is a VERY bad option since it will force a full scan.

Other solution may be to create a function based index.

I hope this helps.
Previous Topic: Regarding declarations in SP's
Next Topic: Oracle Database 10g Express Edition Backup Question
Goto Forum:
  


Current Time: Fri Dec 09 23:17:29 CST 2016

Total time taken to generate the page: 0.11727 seconds