With most functions, including replace(), if any of the arguments are null,
it returns null.
On Mon, Jun 10, 2019 at 4:29 PM Tony Papadimitriou <to...@acm.org> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
