SQL comparison string

Question

CASE WHEN ' 1a' = ' 1a ' THEN 'EQUAL - but it isn´t- HELP!!!!' ELSE 'UNEQUAL' END from dual; 

Can someone help me, explain to me why the databases say these 2 rows are the same

 ' 1a' = ' 1a ' 

I have already tried many applications, but I cannot understand why. I also searched in the Oracle 11g specification, but nothing of the kind was explained.

Answer 1

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

 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; 

Answer 2

In addition to Frank Schmitt 's solution, read about this issue in this forum:

http://asktom.oracle.com/pls/asktom/f?p=100:11:59::::P11_QUESTION_ID:59852033114407

Another solution to this problem is not using a comparison predicate, but some other function that performs comparisons, for example. DECODE() , which does not force data types:

 SELECT CASE WHEN decode(' 1a', ' 1a ', 1, 0) = 1 THEN 'EQUAL - but it isn´t- HELP!!!!' ELSE 'UNEQUAL' END FROM DUAL; 

Answer 3

For more information, you can refer to the documentation here , which says: “In terms and conditions, Oracle treats text literals as if they are of the CHAR data type, comparing them using whitespace comparative semantics” and here for detailed whitespace semantic comparisons .