Trying to Replace Blank value (may be null, may be space char) with 'N/A'

Database
Enthusiast

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

1 REPLY
Junior Contributor

Re: Trying to Replace Blank value (may be null, may be space char) with 'N/A'

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).