March 31, 2002 at 3:23 am
Am I overlooking an obvious explanation, or is this a bug in the replace function? Issuing the following statement in SQL 2000 (sp2):
: select replace('original', 'not present', 'no effect')
returns 'original', as you'd expect. However:
: select replace('original', 'not present', null)
returns a null value.
Now, fair enough, if the statement had been:
: select replace('original', 'rig', null)
then that's what I'd expect, but if the characters to be substituted aren't present in string 1, the value of the third parameter should surely be irrelevant. Or not?
Cheers
John
March 31, 2002 at 7:29 am
Can't say that the behavior makes sense. Then again, why would you try to replace a portion of a string with a null? I'd guess that since nulls propagate somehow replace always ends up with a null result.
Andy
March 31, 2002 at 9:24 am
I'm not sure there are too many occasions where you'd deliberately use a null value in this way - it was a mistake in the code that brought the problem to light.
The function's behaviour seems highly questionable though.
March 31, 2002 at 10:33 am
Here's the explanation, which I found on the MS website...
Regardless of whether or not the second string is contained in the first string, if you pass NULL as any argument to the REPLACE function, it returns NULL. One of the first things that the REPLACE function does is to make sure that it is possible to perform the replace. Because replacing a portion of a string with an unknown value results in an unknown value, it is not possible.
March 31, 2002 at 5:31 pm
Thanks for the follow-up. That really is odd behavior, because no match is found so I'd have thought they would have coded nothing to happen and the original string would be returned. This sounds like a prime candidate for a CASE statement to get the appropriate behavior rather than just a replace alone.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 1, 2002 at 9:02 am
Yes, it is similar to return a concatination, a null in any value will return a null. I am sure that it uses the same check mechanism.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply