OraFAQ Forum: SQL & PL/SQL » char comparision

Home » SQL & PL/SQL » SQL & PL/SQL » char comparision

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

char comparision [message #306120]

Wed, 12 March 2008 22:18

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

Report message to a moderator

Re: char comparision [message #306123 is a reply to message #306120]

Wed, 12 March 2008 22:53

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,

Report message to a moderator

Re: char comparision [message #306124 is a reply to message #306120]

Wed, 12 March 2008 22:55

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Oracle is using blank-padded comparison to evaluate the literals.

Report message to a moderator

Re: char comparision [message #306127 is a reply to message #306123]

Wed, 12 March 2008 23:08

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

Report message to a moderator

Re: char comparision [message #306128 is a reply to message #306124]

Wed, 12 March 2008 23:12

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

Report message to a moderator

Re: char comparision [message #306129 is a reply to message #306120]

Wed, 12 March 2008 23:40

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,

Report message to a moderator

Re: char comparision [message #306134 is a reply to message #306120]

Thu, 13 March 2008 00:57

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.

Report message to a moderator

Re: char comparision [message #306138 is a reply to message #306134]

Thu, 13 March 2008 01:01

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.

Report message to a moderator

Re: char comparision [message #306140 is a reply to message #306134]

Thu, 13 March 2008 01:08

Littlefoot
Messages: 21809
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.

Report message to a moderator

Re: char comparision [message #306141 is a reply to message #306138]

Thu, 13 March 2008 01:11

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.

Report message to a moderator

Re: char comparision [message #306148 is a reply to message #306141]

Thu, 13 March 2008 01:17

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 ??

Report message to a moderator