Home » SQL & PL/SQL » SQL & PL/SQL » char comparision
char comparision [message #306120] Wed, 12 March 2008 22:18 Go to next message
yes_rajes
Messages: 4
Registered: November 2007
Location: Singapore
Junior Member
Hi,

How does the following query returns 'TRUE' even though there is an additional space in the right side of the where condition.

Does it mean that the spaces on the right end of the characters will be trimmed by default?

If so, how can i avoid this situation.

SQL> select 'TRUE' from dual where 'rajes' = 'rajes ';

'TRU
----
TRUE

Please clarify me.

Thanks in adv.

Regards,
Rajesh
Re: char comparision [message #306123 is a reply to message #306120] Wed, 12 March 2008 22:53 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member

SQL> ed
Wrote file afiedt.buf

  1* select decode('rajesh','rajesh','true','false') from dual
SQL> /

DECO
----
true

SQL> ed
Wrote file afiedt.buf

  1* select decode('rajesh','   rajesh','true','false') from dual
SQL> /

DECOD
-----
false

SQL> ed
Wrote file afiedt.buf

  1* select decode('rajesh','rajesh   ','true','false') from dual
SQL> /

DECOD
-----
false

SQL> ed
Wrote file afiedt.buf

  1* select decode('rajesh','   rajesh   ','true','false') from dual
SQL> /

DECOD
-----
false

SQL> 



regards,
Re: char comparision [message #306124 is a reply to message #306120] Wed, 12 March 2008 22:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Oracle is using blank-padded comparison to evaluate the literals.
Re: char comparision [message #306127 is a reply to message #306123] Wed, 12 March 2008 23:08 Go to previous messageGo to next message
yes_rajes
Messages: 4
Registered: November 2007
Location: Singapore
Junior Member
Hi mshrkshl,

Thanks for the response.
It works fine for the given query.
For better understanding i had hard coded the where condition.

But what I really want to know is how to apply this across the tables.

For ex:
select ctbl1.* from customer ctbl1, customer ctbl2
where ctbl1.customername = ctbl2.customername
and ctbl1.customerid <> ctbl2.customerid

In this scenario customer name 'Rajesh' and 'Rajesh ' should be treated as unequal.

Thanks & Regards,
Rajesh
Re: char comparision [message #306128 is a reply to message #306124] Wed, 12 March 2008 23:12 Go to previous messageGo to next message
yes_rajes
Messages: 4
Registered: November 2007
Location: Singapore
Junior Member
Thanks Ebrian,

Is it possible to override the blank-padded comparison?
If so, how?

Regards,
Rajesh
Re: char comparision [message #306129 is a reply to message #306120] Wed, 12 March 2008 23:40 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
one way maybe replace '' by any character.

SQL> ed
Wrote file afiedt.buf

  1* select 'TRUE' from dual where 'rajes' = replace('rajes ','','*')
SQL> /

no rows selected


regards,
Re: char comparision [message #306134 is a reply to message #306120] Thu, 13 March 2008 00:57 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

Can you tell which oracle version your using?


SQL> select *from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



SQL> select 'true' from dual where 'thani'=' thani';

no rows selected

SQL> select 'TRUE' from dual where 'rajes' = 'rajes ';

'TRU
----
TRUE

SQL> select 'TRUE' from dual where 'thani'='thani';

'TRU
----
TRUE

SQL> select 'true' from dual where 'thani'='thani';

'TRU
----
true

SQL> select 'true' from dual where 'thani'=' thani';

no rows selected

SQL> select 't' from dual where 'thani'='thani';

'
-
t

SQL> select 't' from dual where 'thani'=' true';

no rows selected

SQL> select 't' from dual where 'thani'='true ';

no rows selected

Thanks,
Thani.
Re: char comparision [message #306138 is a reply to message #306134] Thu, 13 March 2008 01:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Oracle version doesn't matter. The blank-padding is a SQL standard. The padding only occurs to the right of the literal.
Re: char comparision [message #306140 is a reply to message #306134] Thu, 13 March 2008 01:08 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How much would it cost if you (people who don't do that) start using [code] tags so that I (half-blind person) would see what you are, actually, trying to show with your examples? Sniper isn't enough, I'll have to start using a telescope to find out where that "extra space" you are mentioning is.
Re: char comparision [message #306141 is a reply to message #306138] Thu, 13 March 2008 01:11 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

What is the diff b/w this?

SQL> select 't' from dual where 'thani'=' true';

no rows selected

SQL> select 't' from dual where 'thani'='true ';

no rows selected


SQL> select 't' from dual where 'thani'=' true ';

no rows selected


Thanks,
Thani.
Re: char comparision [message #306148 is a reply to message #306141] Thu, 13 March 2008 01:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
thani_oracle wrote on Thu, 13 March 2008 02:11

What is the diff b/w this?

SQL> select 't' from dual where 'thani'=' true';

no rows selected

SQL> select 't' from dual where 'thani'='true ';

no rows selected


SQL> select 't' from dual where 'thani'=' true ';

no rows selected



Have no idea what you are trying to prove with this?

Of course these aren't equal ??
Re: char comparision [message #306183 is a reply to message #306141] Thu, 13 March 2008 03:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
thani_oracle wrote on Thu, 13 March 2008 07:11
Hi,

What is the diff b/w this?

SQL> select 't' from dual where 'thani'=' true';

no rows selected

SQL> select 't' from dual where 'thani'='true ';

no rows selected


SQL> select 't' from dual where 'thani'=' true ';

no rows selected


Thanks,
Thani.

Now that you found the bold-tag, it's only a small step to use the code-tag.
You've been warned often enough now.

[Updated on: Thu, 13 March 2008 03:09]

Report message to a moderator

Re: char comparision [message #306211 is a reply to message #306120] Thu, 13 March 2008 05:17 Go to previous message
yes_rajes
Messages: 4
Registered: November 2007
Location: Singapore
Junior Member
Anyhow, thanks to everyone.

Regards,
Rajesh
Previous Topic: How to Check the date for the statistics last generated
Next Topic: can we create a table
Goto Forum:
  


Current Time: Fri Dec 02 14:25:51 CST 2016

Total time taken to generate the page: 0.60317 seconds