invalid identifier '"null"' (snowflake)-CodePudding

My column $1 has a few "null" strings that I want to replace with NULLs. I am trying this:

SELECT
    REGEXP_REPLACE( $1 , "null",  NULL) AS "JSON_DATA"
 FROM NEW_TABLE

However, I am getting an error that:

SQL Error [904] [42000]: SQL compilation error: error line 2 at position 22
invalid identifier '"null"'

This is the syntax I see in the documentation. What am I doing wrong?

CodePudding user response：

the double quotes need to be in a normal string

SELECT
    REGEXP_REPLACE( $1 , '"null"',  NULL) AS "JSON_DATA"
FROM TEST_TABLE

As you have it, the DB is looking for a columns named null, that what snowflake reads double quotes as.

You might also want to look at STRIP_NULL_VALUE and/or IF_NULL_VALUE

CodePudding user response：

If you mean the values are actually string with value null (without the double quotes), then your query should be:

SELECT
    REGEXP_REPLACE( $1 , 'null',  NULL) AS "JSON_DATA"
 FROM NEW_TABLE

As others already mentioned, double quoted strings are identifiers in Snowflake, use single quoted strings for actual string values.