SQL compare string

Max Müller picture Max Müller · Aug 21, 2012 · Viewed 9.3k times · Source
CASE 
WHEN ' 1a' = ' 1a                 '
THEN 'EQUAL - but it isn´t- HELP!!!!' ELSE 'UNEQUAL'
END                     
from dual;

Can anybody help me, an explain to me why the DB say that these 2 strings are the same

' 1a' = ' 1a                 '

I already tried a lot of statements but I can not find out why. I searched as well in the specification of Oracle 11g but there is nothing explained like that.

Answer

Frank Schmitt picture Frank Schmitt · Aug 21, 2012

Because Oracle converts your string literal to a CHAR; since CHAR is of fixed length, it has to expand the shorter string to the correct length, and therefore adds spaces. Try VARCHAR2 instead:

SELECT
  CASE 
  WHEN cast(' 1a' as varchar2(100)) = cast(' 1a                 ' as varchar2(100))
  THEN 'EQUAL - but it isn´t- HELP!!!!' ELSE 'UNEQUAL'
  END                     
from dual;

EDIT: example to illustrate the difference between CHAR(10) and VARCHAR2(10)

declare
  l_char1 char(10) := '1';
  l_char2 char(10) := '1    ';
  l_varchar1 varchar2(10) := '1';
  l_varchar2 varchar2(10) := '1    ';
begin
  if l_char1 = l_char2 then     
    dbms_output.put_line('char equal');
  else
    dbms_output.put_line('char NOT equal');
  end if;  
  if l_varchar1 = l_varchar2 then 
    dbms_output.put_line('varchar equal');
  else
    dbms_output.put_line('varchar NOT equal');
  end if;
end;