when we need trim when we don't need in oracle

Question

In Oracle I see, some time 'A ' = 'A', but some time 'A ' != 'A', Please see example below you will see detail. I has been try to short the code.

CREATE TABLE AAA (
    T1 CHAR(3),
    T2 VARCHAR(3),
    T3 VARCHAR(5)
);
insert into AAA
SELECT 'A','A  ', 'A    ' FROM DUAL;
COMMIT;
SELECT * FROM AAA WHERE T1 = 'A'
UNION ALL
SELECT  * FROM AAA WHERE T2 = 'A  '
UNION ALL
SELECT  * FROM AAA WHERE T2 = 'A    '
UNION ALL
SELECT  * FROM AAA WHERE TRIM(T2) = TRIM('A   ')
UNION ALL
SELECT CASE WHEN 'A' = 'A ' THEN '1' ELSE '0' END T1, 
       CASE WHEN 'A       ' = 'A' THEN '1' ELSE '0' END T2,
       CASE WHEN 'A ' = 'A ' THEN '1' ELSE '0' END T2   
FROM DUAL;

I ask this question because in my db, When I join table A to table B some time we TRIM(A.ID) = TRIM(B.REFID) some time we don't need to do this. When we need TRIM(column)? When 'A ' = 'A' ? and when 'A ' != 'A'?

Answer 1

I think the behavior your are seeing when querying your actual table is expected, and the only behavior which might surprise you is in the following query:

SELECT CASE WHEN 'A' = 'A ' THEN '1' ELSE '0' END T1, 
       CASE WHEN 'A       ' = 'A' THEN '1' ELSE '0' END T2,
       CASE WHEN 'A ' = 'A ' THEN '1' ELSE '0' END T2   
FROM DUAL;

From this Oracle discussion, it appears that what is happening has to do with that 'A', 'A ', etc. are char types and not varchar2 types. As a result, in order to compare two char values they need to be the same length. Consider the following CASE expression:

CASE WHEN 'A       ' = 'A' THEN '1' ELSE '0' END T2

Here the 'A' literal, which is a char(1), will be promoted to char(8) by padding on the right with spaces. So when the comparsion actually takes place, both the LHS and RHS are actually the same. Note that the following CASE expression would not evaluate to the true condition:

CASE WHEN '  A' = 'A' THEN '1' ELSE '0' END T2

This is because the padding would happen to 'A' on the right, so ' A' would be compared against 'A ', which are not the same.

Follow the link below for a demo which shows that if you use all varchar columns that the original spaces/widths of the inputs are preserved, and the logical comparison of strings is what you would expect.

Demo

Answer 2

Looking at the Oracle Documentation on literals:

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

• Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.

and the documentation of blank-padded comparison semantics states:

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

So, for your examples:

SELECT * FROM AAA WHERE T1 = 'A'

Since the left-hand side of the comparison, T1 which equals 'A ', is a CHAR(3) and the right-hand side is a text literal 'A' then blank-padded comparison semantics are used and 'A ' = 'A' is true.

Then:

SELECT  * FROM AAA WHERE T2 = 'A  '

The left-hand side of the comparison, T2 which equals 'A ', is a VARCHAR2(3) and the right-hand side is a text literal 'A ' then blank-padded comparison semantics are not used (both sides are not CHAR or text literals) but the values are equal ('A ' = 'A ') so the condition is true.

And:

SELECT  * FROM AAA WHERE T2 = 'A    '

The LHS is a VARCHAR2 equal to 'A ' and the RHS is a text literal equal to 'A '; blank-padded comparison semantics will not be used and the values are not equal so the condition is not true and the row will not be returned.

Then:

SELECT  * FROM AAA WHERE TRIM(T2) = TRIM('A   ')

The TRIM() function states that:

The string returned is of VARCHAR2 datatype if trim_source is a character datatype and a LOB if trim_source is a LOB datatype.

Both values are trimmed so you have the condition 'A' = 'A' where both sides are a VARCAHR2 data type so blank-padded comparison semantics will not be used but the values are equal so the condition is true and the row will be returned.

Finally:

SELECT CASE WHEN 'A' = 'A ' THEN '1' ELSE '0' END T1, 
       CASE WHEN 'A       ' = 'A' THEN '1' ELSE '0' END T2,
       CASE WHEN 'A ' = 'A ' THEN '1' ELSE '0' END T2   
FROM DUAL;

In all cases both sides of the equality are text literals so blank-padded comparison semantics will be used so for

• 'A' = 'A ' the LHS will be padded so the strings are the same length;
• 'A ' = 'A' the RHS will be padded;
• 'A ' = 'A ' both sides are equal length so no padding is needed.

And in each case the equalities are equal.

If you use CAST to change from text literals to a VARCHAR2 data type:

SELECT CASE WHEN 'A' = CAST( 'A ' AS VARCHAR2(5) ) THEN 1 ELSE 0 END AS T1
FROM   DUAL

The LHS is a text literal but the RHS is a VARCHAR2 and blank-padded comparison semantics are not used and the equality is not true.