CODEWITHSUNDEEP
sqlpostgresqlsnowflake-cloud-data-platformsnowflake-schema
user13067694
user13067694

Reputation:

invalid identifier '"null"' (snowflake)

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?

Upvotes: 1

Views: 674

Answers (2)

Eric Lin
Eric Lin

Reputation: 1520

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.

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

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

Upvotes: 3

Related Questions