SQL: Calling REPLACE() with a NULL replacement

Question

I've recently run into this behaviour and whilst I can work around it, I'm quite curious as to why it occurs. There doesn't seem to be much documentation explaining why this happens.

Consider the following two snippets:

SELECT REPLACE('hello world', 'world', NULL)    
SELECT REPLACE('hello world', 'foobar', NULL)

Both of these examples return NULL.

I don't really understand either, but with the first one I can see how if there is indeed some caveat I'm unaware of with NULLs that this will cause the outcome we see.

But with the second example I'm completely stumped. Even if there is some strange NULL replacing behaviour, why is my string being manipulated at all? The replacement string is not found in the target string.

The documentation for replace clearly states that it Returns NULL if any one of the arguments is NULL. It's listed in the second paragraph under Return Types — jpw
– jpw, Commented Mar 5, 2018 at 11:27
Downvote reason: Please read the documentation, especially if your question complains about it. — Zohar Peled
– Zohar Peled, Commented Mar 5, 2018 at 11:30
Replacing any part of a string with NULL is not a meaningful operation to begin with. Think of it as the function validating its arguments, seeing something invalid and immediately bailing out. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 5, 2018 at 11:32
I'm going to take a stab at why this is the case, but I don't have any proof and can't find evidence. You would think they would look for the string and replace it if found, but most high performing things are done by bit comparisons, shifting, OR'ing, or AND'ing. My bet would be that behind the scenes, they are AND'ing the string (or parts of it) at a binary level. Doing this with NULL results in NULL. — UnhandledExcepSean
– UnhandledExcepSean, Commented Mar 5, 2018 at 11:42
@UnhandledExcepSean: consider the possibility that it's implemented exactly how the documentation says it is: if (isNull(arg1) || isNull(arg2) || isNull(arg3)) return DB_NULL; /* all arguments are non-NULL, do regular replacement */. Getting the special cases out of the way greatly simplifies things. (And note that NULL is not the null terminator or an otherwise blank string, so it is a special case.) — Jeroen Mostert
– Jeroen Mostert, Commented Mar 5, 2018 at 11:56

paparazzo · Accepted Answer · 2018-03-05 14:23:50Z

3

Read this:

https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

It states that:

Returns NULL if any one of the arguments is NULL.

edited Mar 5, 2018 at 14:23

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

answered Mar 5, 2018 at 11:34

MJH

1,7481 gold badge12 silver badges20 bronze badges

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

Comments

Tyron78 · Accepted Answer · 2018-03-05 11:30:07Z

0

This is most certainly a result of the messed up NULL handlich in SQL Server. Everything you perform with NULL will result in NULL as well. Try SELECT 1 + NULL or 'abc' + NULL. Anyways, since you are working with a string, you should use the empty string '' instead of NULL.

answered Mar 5, 2018 at 11:30

Tyron78

4,1973 gold badges21 silver badges32 bronze badges

7 Comments

Zohar Peled Over a year ago

SQL Server is handling nulls just fine, most of the cases. There are just few places where you can honestly say it messed things up, such as order by and set ansi-nulls off.

Tyron78 Over a year ago

We recently had an issue, where a developer left joined some tables - one contained prices and the other one VAT percentages. However, unfortunately he didn't consider, that PRICE + (PRICE * VAT_Perc) results in NULL, when his right table doesn't contain a VAT percentage for a given timespan...

Zohar Peled Over a year ago

The fact that your developer failed to handle what is a very common situation working with databases does not mean that SQL Server handles nulls wrong...

Tyron78 Over a year ago

Yeah, OK. So the NULL handling is fine - nevertheless, REPLACE should be used with '' instead of NULL :-P

Zohar Peled Over a year ago

replace should not be used with null, unless you want it to return null. It's a well documented behavior. Don't go trashing a product because you or your team member failed to read or understand the documentation... I'm not saying SQL Server is bug free, but that this case is not a bug.

|

Collectives™ on Stack Overflow

SQL: Calling REPLACE() with a NULL replacement

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related