Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sokrates.

Asked: March 22, 2006 - 10:23 am UTC

Last updated: August 12, 2016 - 3:26 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

<Fred Feuerstein writes>

I recently received an email from a puzzled developer. So...what's wrong with the following scenario?

SQL> SELECT 'SQL IN trimmed trailing blanks!'
2 FROM DUAL
3 WHERE 'CA ' IN ( 'CA', 'US' )
4 /

'TRIMMEDTRAILINGBLANKS!'
------------------------
SQL IN trimmed trailing blanks!

Isn't that odd? I asked if the string "CA " (CA followed by three spaces) was in the list of 'CA' and 'US' -- and Oracle said yes!

I checked the documentation but could not find anything about automatically trimming blanks when you use the IN clause. So I experimented some more:

....

Does not trim leading spaces:

SQL> SELECT 'SQL IN does not trim leading blanks!'
2 FROM DUAL
3 WHERE ' CA' IN ( 'CA', 'US' )
4 /

no rows selected

- And most important of all: does not trim anything at all when you execute the same query inside a PL/SQL block or simply use the native PL/SQL IN clause:

DECLARE
PROCEDURE exec_in ( message_in IN VARCHAR2, value_in IN VARCHAR2 )
IS
l_dummy VARCHAR2 ( 32767 );
BEGIN
BEGIN
SELECT message_in
INTO l_dummy
FROM DUAL
WHERE value_in IN ( 'CA', 'US' );

DBMS_OUTPUT.put_line ( message_in );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( message_in || ' did not happen.' );
END;

IF value_in IN ( 'CA', 'US' )
THEN
DBMS_OUTPUT.put_line ( '"PL/SQL IN ' || SUBSTR ( message_in, 9 ));
ELSE
DBMS_OUTPUT.put_line ( '"PL/SQL IN '
|| SUBSTR ( message_in, 9 )
|| ' did not happen.'
);
END IF;
END;
BEGIN
exec_in ( '"SQL IN trimmed trailing blanks."', 'CA ' );
exec_in ( '"SQL IN trimmed leading blanks."', ' CA' );
END;
/

"SQL IN trimmed trailing blanks." did not happen.
"PL/SQL IN trimmed trailing blanks." did not happen.
"SQL IN trimmed leading blanks." did not happen.
"PL/SQL IN trimmed leading blanks." did not happen.

The lesson we learn once again: write everything in PL/SQL!

Ah, just kidding. You should continue to choose the best tool for the challenge at hand. But in this case, avoid funkiness with IN by executing your query within a PL/SQL block!

</Fred Feuerstein writes>

So, I still don't understand this.
Is it a bug ?
(I can reproduce it in 9.2)





and Tom said...

This is not a bug.

This is expected. By default 'CA' and 'CA ' are 'char' types - not varchar2 types.

When comparing char to char - SQL says to promote the type - so we would be comparing all char(3) to all char(3) - taking the 2 character char's and making them char(3)'s which blank pads at the end.

Hence

ops$tkyte@ORA9IR2> select * from dual where 'CA ' = 'CA';

D
-
X

returns a row since both literals are CHAR(3)'s by SQL definition and 'CA' becomes 'CA ', but:

ops$tkyte@ORA9IR2> select * from dual where ' CA' = 'CA';

no rows selected

doesn't return a thing because it is equivalent to:

ops$tkyte@ORA9IR2> select * from dual where ' CA' = 'CA ';

no rows selected


the blanks are added to the END of the string

if you use varchar2 types


ops$tkyte@ORA9IR2> select * from dual where cast( 'CA ' as varchar2(3) ) = 'CA';

no rows selected

this doesn't happen This is why I don't like the char type AT ALL and never use it. From Expert Oracle Database Architecture, I wrote:

<quote>
The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. Let’s use the 'Hello World' string in a simple table to demonstrate:

ops$tkyte@ORA10G> create table t
2 ( char_column char(20),
3 varchar2_column varchar2(20)
4 )
5 /
Table created.

ops$tkyte@ORA10G> insert into t values ( 'Hello World', 'Hello World' );
1 row created.

ops$tkyte@ORA10G> select * from t;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
ops$tkyte@ORA10G> select * from t where char_column = 'Hello World';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

ops$tkyte@ORA10G> select * from t where varchar2_column = 'Hello World';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

So far, the columns look identical but, in fact, some implicit conversion has taken place and the CHAR(11) literal has been promoted to a CHAR(20) and blank padded when compared to the CHAR column. This must have happened since Hello World……… is not the same as Hello World without the trailing spaces. We can confirm that these two strings are materially different:

ops$tkyte@ORA10G> select * from t where char_column = varchar2_column;
no rows selected

They are not equal to each other. We would have to either blank pad out the VARCHAR2_COLUMN to be 20 bytes in length or trim the trailing blanks from the CHAR_COLUMN, as follows:

ops$tkyte@ORA10G> select * from t where trim(char_column) = varchar2_column;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
ops$tkyte@ORA10G> select * from t where char_column = rpad( varchar2_column, 20 );
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

Note There are many ways to blank pad the VARCHAR2_COLUMN, such as using the CAST() function.

The problem arises with applications that use variable length strings when they bind inputs, with the resulting “no data found” that is sure to follow:

ops$tkyte@ORA10G> variable varchar2_bv varchar2(20)
ops$tkyte@ORA10G> exec :varchar2_bv := 'Hello World';
PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select * from t where char_column = :varchar2_bv;
no rows selected

ops$tkyte@ORA10G> select * from t where varchar2_column = :varchar2_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

So here, the search for the VARCHAR2 string worked, but the CHAR column did not. The VARCHAR2 bind variable will not be promoted to a CHAR(20) in the same way as a character string literal. At this point, many programmers form the opinion that “bind variables don’t work; we have to use literals.” That would be a very bad decision indeed. The solution is to bind using a CHAR type:

ops$tkyte@ORA10G> variable char_bv char(20)
ops$tkyte@ORA10G> exec :char_bv := 'Hello World';

PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where char_column = :char_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
ops$tkyte@ORA10G> select * from t where varchar2_column = :char_bv;
no rows selected

However, if you mix and match VARCHAR2 and CHAR, you’ll be running into this issue constantly. Not only that, but the developer is now having to consider the field width in her applications. If the developer opts for the RPAD() trick to convert the bind variable into something that will be comparable to the CHAR field (it is preferable, of course, to pad out the bind variable, rather than TRIM the database column, as applying the function TRIM to the column could easily make it impossible to use existing indexes on that column), she would have to be concerned with column length changes over time. If the size of the field changes, then the application is impacted, as it must change its field width.

It is for these reasons—the fixed-width storage, which tends to make the tables and related indexes much larger than normal, coupled with the bind variable issue—that I avoid the CHAR type in all circumstances. I cannot even make an argument for it in the case of the one-character field, because in that case it is really of no material difference. The VARCHAR2(1) and CHAR(1) are identical in all aspects. There is no compelling reason to use the CHAR type in that case, and to avoid any confusion, I “just say no,” even for the CHAR(1) field.
</quote>



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sokrates, March 23, 2006 - 2:47 pm UTC


Herod, March 23, 2006 - 4:33 pm UTC


A reader, March 23, 2006 - 5:08 pm UTC

This is amazing. Looks like it is an implementation of SQL standards. After trying this in Oracle, I asked a fellow SQL server DBA to try this kind of comparison in SQL server and it worked. We did as

select * from t where 'CA '='CA'

and it returned rows!! Even he was amazed. Ok time to get back to the concepts manual once again...

amazing....

A reader, March 23, 2006 - 7:30 pm UTC


Can you please elaborate

A reader, March 23, 2006 - 11:17 pm UTC

Can you Pls explain :
</

When comparing char to char - SQL says to promote the type - so we would be
comparing all char(3) to all char(3) - taking the 2 character char's and making
them char(3)'s which blank pads at the end.

Hence

ops$tkyte@ORA9IR2> select * from dual where 'CA ' = 'CA';

D
-
X

returns a row since both literals are CHAR(3)'s by SQL definition and 'CA'
becomes 'CA ',

/>

Why does SQL does that. Looks like a SQL Standard rather than Oracel issue.

How can a literal char(3) be equal to literal CHAR(2) i.e 'CA ' be equal to 'CA'

Why does SQL do this implicit padding when comparing.
Why does the length function then give
Length('CA') = 2 AND LENGTH ('CA ') = 3

select length('CA ') , length( 'CA') from dual where 'CA ' = 'CA'
LENGTH('CA') LENGTH('CA')
------------ ------------
3 2
1 row selected

select * from dual where 'CA ' = 'CA' and length('CA ') = length( 'CA')

gives no rows.





Tom Kyte
March 24, 2006 - 8:29 am UTC

... Why does SQL does that ...

you would have to ask the SQL committee that question - they made the standard, we follow it.



Some Pl/sql example same behavior for above posting

A reader, March 23, 2006 - 11:24 pm UTC

BEGIN
IF 'CA' = 'CA '
THEN
DBMS_OUTPUT.PUT_LINE (' That is funny 3$ equals 2$');
ELSE
DBMS_OUTPUT.PUT_LINE (' That is not funny 3$ <> 2$ ');
END IF;
END;

o/p:
That is funny 3$ equals 2$

Length function

A reader, March 24, 2006 - 12:43 am UTC

There's no need for the length function to do datatype conversion in the way your example uses it. The length function can be evaluated and value returned before the lengths are compared.
You have to do the conversion explicitly.
select length (cast ('CA' as char(3))), length (cast ('CA ' as char(3))) from dual;

Helena Marková, March 24, 2006 - 4:36 am UTC


Mirjana, March 24, 2006 - 8:43 am UTC


CHAR(1)

Uwe M. Kuechler, April 24, 2006 - 1:06 pm UTC

Tom,

I've been following the "avoid CHAR"-recommendations for the last years, with one exception: 1-character-fields.

My understanding (what I've been taught) of the VARCHAR2 Datatype was that it uses an additional Byte as a string terminator while CHAR doesn't need such. That may save a considerable amount of space in some cases - like tables with a lot of status flags.
When I tried to verify that with table statistics on 2 tables with a varchar2(1) and a char(1) column, I found that the average row length was 2 in both cases. Why is that? CHAR isn't terminated, is it?

Could you shed some light on this?

Best regards,
--==/ Uwe \==--

Tom Kyte
April 24, 2006 - 1:24 pm UTC

you have been taught wrong.

a char is a varchar2 - only a char is blank padded to the maximum lenght.

Hence a non-null varchar2(1) and a non-null char(1) - exactly the same, 100% the same.

A null varchar2(1) and a null char(1) - exactly the same, 100% the same.



Both just have a leading LENGTH - only a char is always the maximum length, whereas a varchar2 is not.

any global parameter change is introduced

banerjee, March 12, 2011 - 1:09 pm UTC

Thanks for the detailed description on this.

But is their any global parameter change so that we can compare the trailing space for varchar2 data types.

In sql server their is option like set ansi_padding on. This option enable user to compare trailing space datas. anythig like that in oracle.
Tom Kyte
March 12, 2011 - 5:09 pm UTC

I don't know what you mean. Give more detail.

If a varchar2 has trailing spaces - they already "count", we don't remove them.

Oracle 'intelligently' strips trailing space in DMLS for CHAR columns

A reader, June 23, 2012 - 6:58 am UTC

In some of our database, DMLs automatically strip trailing blank space even for CHAR columns. This has driven our developers crazy and me completely puzzled! It almost like we have some sort of invisible ANSI_PADDING parameter inside Oracle. I have filed a SR with Oracle support and I haven't got a decent answer.I would greatly appreciate if you could please shed some lights on this. 

Here is what I am talking about ... 

A) In few 10g databases, the insert failed because bause it tried to insert 't1 ' to a CHAR(2) column. This is correct and normal behavior!

SQL> create table test_char(cappt2 char(2), cappt3 char(3), vappt varchar2(3));

Table created.
SQL> desc test_char;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CAPPT2                                             CHAR(2)
 CAPPT3                                             CHAR(3)
 VAPPT                                              VARCHAR2(3)
SQL> insert into test_char values('t1 ','t1 ','t1 ');
insert into test_char values('t1 ','t1 ','t1 ')
                             *
ERROR at line 1:
ORA-12899: value too large for column "XZHENG"."TEST_CHAR"."CAPPT2" (actual: 3,
maximum: 2)

B): the same insert works in other Oracle databases! It seems this Oracle database is intelligent enough to automatically strip trailing blank space 't1 ' to fit in a CHAR(2)!!! How?

SQL> create table test_char(cappt2 char(2), cappt3 char(3), vappt varchar2(3));

Table created.

SQL> desc test_char;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CAPPT2                                             CHAR(2)
 CAPPT3                                             CHAR(3)
 VAPPT                                              VARCHAR2(3)

SQL> insert into test_char values('t1 ', 't1 ', 't1 ');

1 row created.

SQL> select length(cappt2), length(cappt3), length(vappt) from test_char;

LENGTH(CAPPT2) LENGTH(CAPPT3) LENGTH(VAPPT)
-------------- -------------- -------------
             2              3             3







Tom Kyte
June 23, 2012 - 1:59 pm UTC

please report back the output of:

select name || '=' || value from v$parameter where isdefault='FALSE'

from a database that fails, and a database that works

A reader, June 24, 2012 - 5:16 pm UTC

Thank you very much for your follow up. Your query is very useful and I wish I ran it in both databases awhile ago. There is a 'blanking_trimming' parameter that is set to 'TRUE' in the database that works. It defaults to 'FALSE' in the database fails. Oracle support finally got back to me with the right answer last night. Thanks again!

Not always one blank space equals two blank spaces

Vasile, August 04, 2016 - 1:12 pm UTC

Ok, I understand, oracle upscale one space to two, both next cases return 1:
select count(*) from dual where ' ' = ' ';
select count(*) from dual where ' ' in (' ');

Why not here also?
select case when ' ' = ' ' then 1 else 0 end from dual;
select case ' ' when ' ' then 1 else 0 end from dual;
select decode(' ',' ',2,' ',1,0) end from dual;

Connor McDonald
August 06, 2016 - 1:35 pm UTC

You need to use the "code" tag, so we know exactly how many spaces you had in each example

not always 1 blank space = 2 blank spaces

Vasile, August 11, 2016 - 7:14 pm UTC

Ok, I understand, oracle upscale one space to two, both next cases return 1:
select count(*) from dual where ' ' = '  '; 
select count(*) from dual where ' ' in ('  '); 


Why not here also?
select case when ' ' = '  ' then 1 else 0 end from dual; 
select case ' ' when '  ' then 1 else 0 end from dual; 
select decode(' ','  ',2,' ',1,0) end from dual; 

Chris Saxon
August 12, 2016 - 3:26 am UTC

It depends on what type we choose as the default for the operation.

For example

SQL> select dump(' ') from dual;

DUMP('')
----------------
Typ=96 Len=1: 32


Type 96 is CHAR (not VARCHAR2) so the "upscaling" occurs.

Mu guess is that the implementation of decode,case etc came later in the Oracle history, when varchar2 became the norm.

But yes...an interesting difference I was not aware of.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library