I'm trying to replace all values in a column where the value is 0 with NULL
SELECT score
REPLACE(score, 0, ISNULL)
FROM kpis
WHERE score=0
or
SELECT score
REPLACE(score, 0, ISNULL)
FROM kpis
But I get a syntax error:
ERROR: syntax error at or near "REPLACE"
LINE 2: REPLACE(score, 0, ISNULL)
Replace all values in a column where the value is 0 with NULL
update kpis set score = NULL where score = 0;
Replace all integer values in a column with NULL
update kpis set score = NULL where score ~ '^[0-9]*$'
use case when
SELECT
case when score = 0 then null else score end as score
from kpis
Instead of replace you can use NULLIF(). Below query will set score=null when score is 0.
update kpis set score=NULLIF(score, 0)
Or you can simple use below query as suggested by @Sas.
update kpis set score = NULL where score = 0;
caseexpression instead. (Also, REPLACE is for strings, not integers.)