Snowflake -When value doesn't exist make sure its NULL and not empty-CodePudding

I have the following fb.org_story that returns some rows empty, I want to change that so instead of empty it shows NULL (without creating a file format)

select 
fb.org_story as pvt_story
from prod.facebook fb

CodePudding user response：

you can use coalesce() as well

select coalesce(fb.org_story,'') AS pvt_story
FROM prod.facebook AS fb

CodePudding user response：

NULLIF is a little short than use a full IFF or CASE statement

SELECT
    NULLIF(fb.org_story,'') AS pvt_story
FROM prod.facebook AS fb

But if the string has whitespace around it you might need to TRIM that also, thus I would be inclined to use:

SELECT
    NULLIF(TRIM(fb.org_story),'') AS pvt_story
FROM prod.facebook AS fb

So, if you have NULLs and what them displayed as the text NULL we need to convert to TEXT then COALESCE,NVL,IFNULL the desired output

SELECT
    fb.org_story AS pvt_story
    ,fb.org_story::text AS as_text
    ,NVL(fb.org_story::text, 'NULL') as null_says_null_a
    ,COALESCE(fb.org_story::text, 'NULL') as null_says_null_b
    ,IFNULL(fb.org_story::text, 'NULL') as null_says_null_c
    ,IFF(fb.org_story IS NULL, 'NULL', fb.org_story::text ) as null_says_null_d
FROM VALUES (123), (null) AS fb(org_story)

PVT_STORY	AS_TEXT	NULL_SAYS_NULL_A	NULL_SAYS_NULL_B	NULL_SAYS_NULL_C	NULL_SAYS_NULL_D
123	123	123	123	123	123
		NULL	NULL	NULL	NULL