replace 'null' strings with NULL in sql

Question

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.

Seems a very reasonable approach to me. Not sure why repeating the column reference is considered less desirable. — JNevill
– JNevill, Commented Feb 21, 2022 at 15:59
I agree with JNevill. An "better" solution could be to hunt down the person who thought it was a good idea to use the text 'null' as a value and make them fix the problem. — Bohemian
– Bohemian ♦, Commented Feb 21, 2022 at 16:03

Lukasz Szozda · Accepted Answer · 2022-02-21 16:53:40Z

1

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.

edited Feb 21, 2022 at 16:53

answered Feb 21, 2022 at 16:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

x89 Over a year ago

Still, how would regex_replace be used? REGEXP_REPLACE( <subject> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] )what would the subject be here?

Lukasz Szozda Over a year ago

@x89 First argument for REGEXP_REPLACE should be text so JSON_DATA :"ContactPerson"::STRING

x89 Over a year ago

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.

Lukasz Szozda Over a year ago

@x89 Please see my update why you cannot use REGEXP_REPLACE that way

Collectives™ on Stack Overflow

replace 'null' strings with NULL in sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related