Example:
--- CUT ---
create table t(s text);
insert into t values ('1'),('null'),('3');
.print 'BEFORE'
select rowid,* from t;
update t set s = replace(s,'null',null)
--where s = 'null' --adding this works of course but that’s not my point
;
.print 'AFTER'
select rowid,* from t;
--- CUT ---
The documentation says: “The replace(X,Y,Z) function returns a string formed by
substituting string Z for every occurrence of string Y in string X. The BINARY
collating sequence is used for comparisons. If Y is an empty string then return
X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior
to processing.”
“substituting string Z for every occurrence of string Y” implies that if there
is no occurrence of string Y nothing should happen to the original string,
right?
Accordingly, my expectation is that either:
1. null will remain null as there is not really a string (even empty) that can
truly represent it, or
2. we allow null to be converted to empty string so that the “returns a string”
requirement can be satisfied.
or, maybe
3. using null for the Z part gives an error.
In either [1] or [2] above, however, if the target string (Y part) is not
found, the result should be unaltered.
The replacement seems to occur regardless of the target being found or not.
... and everything becomes null.
Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with it
also, and got the same unexpected behavior.
From a quick look, I haven’t seen anything in their documentation on REPLACE to
justify it either.
I consider this behavior wrong, or (easier way out) the documentation should
make a special note about null behaving the way it does.
Thank you.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
