All Forums Database
arbiswas 21 posts Joined 02/13
05 Aug 2015
Trying to Replace Blank value (may be null, may be space char) with 'N/A'

Hi Experts,
I used below to solve:
CASE WHEN CAST(trim(ABC_INDICATOR) AS VARCHAR(255) ) IS NULL THEN 'N/A' Else CAST(ABC_INDICATOR AS VARCHAR(255) ) End
but it is not working. Its a varchar filed hence zeroifnull function is also not working. Any idea?
 
Thanks,
Arindam

dnoeth 4628 posts Joined 11/04
05 Aug 2015

Hi Arindam,
did you work with Oracle before (where NULL sometimes equals '')?

CASE
   WHEN ABC_INDICATOR = '' OR ABC_INDICATOR IS NULL 
   THEN 'N/A' 
   ELSE CAST(ABC_INDICATOR AS VARCHAR(255)) -- why the cast if it's already a VarChar?
END

Btw, ZeroIfNull and NullIfZero should be rewritten with Standard SQL COALESCE(x,0) and NULLIF(x,0).

Dieter

You must sign in to leave a comment.