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.
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;