COALESCE AND BLANK CHAR FIELD

Database
Enthusiast

COALESCE AND BLANK CHAR FIELD

when Char field is passed to the coalesce function, the function strips the trailing spaces from the value. When the field is blank the function returns an emptry-string (length = 0). Is this behavior the standard behavior? shouldn't the function return the original values as is when 'not null'?

 

create table dev.test2 ( f1 char(5));
insert into dev.test2 values (' ');
insert into dev.test2 values(' a ');
insert into dev.test2 values('aa ');
insert into dev.test2 values(null); /* for char fields null and empty-string are considered same */
select f1, chars(f1) f1_len, type(f1) f1_type, coalesce(f1,'null') f1_coalesce,
chars(coalesce(f1,'null')) f1_coalesce_len,
type(coalesce(f1,'null')) f1_coalesce_Type
from dev.test2

 

f1 f1_len f1_type f1_coalesce f1_coalesce_len f1_coalesce_Type
1 aa 5 CHAR(5) aa 2 VARCHAR(5)
2 a 5 CHAR(5) a 2 VARCHAR(5)
3 5 CHAR(5) 0 VARCHAR(5)
4 ? ? CHAR(5) null 4 VARCHAR(5)

 

  • Database

Accepted Solutions
Junior Contributor

Re: COALESCE AND BLANK CHAR FIELD

Well, COALESCE returns the original value, but the 'null' literal is a VarChar and thus turns the result into VarChar and then trailing spaces are trimmed, this works as documented.

 

When you use coalesce(f1, cast('null' as char(4)) you get what you want.

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: COALESCE AND BLANK CHAR FIELD

Well, COALESCE returns the original value, but the 'null' literal is a VarChar and thus turns the result into VarChar and then trailing spaces are trimmed, this works as documented.

 

When you use coalesce(f1, cast('null' as char(4)) you get what you want.