Transformation of empty string to NULL

[desmondchew]

We are replicating from source MySQL 8 on premise into target Snowflake AWS. We have enabled internal parameter in Snowflake, emptyFieldAsNull=1. Our requirement is to make all blank values to be NULL.

In MySQL, the column "buyer_id" has a default value of null.

Excerpt create table syntax:

`BUYER_ID` varchar(30) COLLATE utf8mb4_0900_as_cs DEFAULT NULL

SELECT po_no,
buyer_Id,
ISNULL(buyer_Id) AS is_it_null,
LENGTH(buyer_Id) AS char_length,
HEX(buyer_Id) AS hex_value
FROM PO
WHERE po_no in ('25001600','25000455','25000458','25000459','50377503');
AND buyer_Id IS NULL OR buyer_Id = ''

You could see from screenshot that only po_no=50377503 is NULL, the rest is blank (empty string).

After replicate is loaded into Snowflake, Snowflake return results the same, only po_no=50377503 appear as NULL, the rest is empty. It's in sync. But the user requirement, they want the empty string to appear as NULL. I have enabled the emptyfieldasNULL parameter yet still same result.

Then I have tried transformation, using replace column value, f(x)=NULLIF(TRIM($AR_CURR_COLUMN_VALUE), '') expression.

But still the value output in Snowflake remain as empty string. How can we get it to be NULL?

Thank you.
Desmond

[DesmondWOO]

Hi @desmondchew ,

Please try applying the following transformation:

CASE
WHEN $BUYER_ID = '' THEN NULL
ELSE $BUYER_ID
END

Regarding the internal parameter emptyFieldAsNull, it does not appear to work in my environment either. I recommend opening a support ticket so the issue can be investigated further.

Regards,
Desmond