Zeroifnull treating blanks (spaces) as null

Database
Fan

Zeroifnull treating blanks (spaces) as null

Hi

 

I have a question for you. I know it's not technically a good idea but someone at my site has used zeroifnull on a char field.

My question is why would it treat blanks (ie spaces) as a null?
Try the following:

 

SELECT CASE WHEN ' ' IS NULL THEN '0' ELSE ' ' END AS cased,
COALESCE(' ',0) AS coalesced,
ZEROIFNULL(' ') AS zeroed;

 

Be interested to know why.

 

Cheers

 

M


Accepted Solutions
rjg
Supporter

Re: Zeroifnull treating blanks (spaces) as null

Merlin,

Since zeroifnull is a numeric function(Teradata extension)  it is not treating blank as null, but is treating it as 0 and is retuning that value similar to casting blank to a numeric value.

 

SyntaxEditor Code Snippet

sel cast (' ' as smallint);
0

 

Zeroifnull

If the operand is character, the format is the default format for FLOAT

 

 

Rglass

1 ACCEPTED SOLUTION
2 REPLIES
rjg
Supporter

Re: Zeroifnull treating blanks (spaces) as null

Merlin,

Since zeroifnull is a numeric function(Teradata extension)  it is not treating blank as null, but is treating it as 0 and is retuning that value similar to casting blank to a numeric value.

 

SyntaxEditor Code Snippet

sel cast (' ' as smallint);
0

 

Zeroifnull

If the operand is character, the format is the default format for FLOAT

 

 

Rglass

Fan

Re: Zeroifnull treating blanks (spaces) as null

Ahh I see - thanks for this :)