CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
x89
x89

Reputation: 3460

replace 'null' strings with NULL in sql

I wanted to check my column. If there was a 'null' string, I wanted to replace it with a NULL value. This works but is there a better way to do it? Such that I don't have to repeat the same thing twice JSON_DATA :"ContactPerson"::STRING

SELECT 
  IFF(JSON_DATA :"ContactPerson"::STRING = 'null',NULL, JSON_DATA :"ContactPerson"::STRING) AS "ContactPerson",
  FROM TEST_TABLE

I want to use REPLACE or REGEX_REPLACE instead.

Upvotes: 1

Views: 1331

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Using IS_NULL_VALUE could be a bit shorter:

SELECT
  IFF(IS_NULL_VALUE(JSON_DATA:"ContactPerson"), NULL, 
       JSON_DATA :"ContactPerson"::STRING)
FROM TEST_TABLE;

or NULLIF:

Returns NULL if expr1 is equal to expr2, otherwise returns expr1.

SELECT NULIF(JSON_DATA :"ContactPerson"::STRING, 'null')
FROM TEST_TABLE;

Regarding comments:

Still, how would regex_replace be used? REGEXP_REPLACE( , [ , , , , ] )what would the subject be here?

REGEXP_REPLACE(JSON_DATA :"Business_Type"::STRING, 'null', NULL) AS "BS2",but this would give me NULL if "null" doesn't exist in the original value

CREATE FUNCTION:

CALLED ON NULL INPUT

Specifies the behavior of the UDF when called with null inputs. In contrast to system-defined functions, which always return null when any input is null, UDFs can handle null inputs, returning non-null values even when an input is null

and REPLACE function has this behaviour described explicitly"

If any of the arguments is a NULL, the result is also a NULL.

Upvotes: 1

Related Questions