Adam Haines (7/12/2010)
I personally think the reason the function returns null is because of NULL concatenation math. I think the underlying code uses loops and concatentates the string piece by piece similar to the construct below. Because an empty string has no length the code cant iterate through the string correctly, thus the returned value is NULL. Even if you could determine the length of the string, the NULL concatenation would make the entire string NULL anyway.
But the REPLACE works just fine with empty strings -- those that have zero length. SQL does not consider strings with NULL value as the same thing as empty strings.
Declare @SearchMe VarChar(50)
Declare @FindMe VarChar(50)
Declare @UseMeInstead VarChar(50)
Set @SearchMe = 'This is a simple sentence.'
Set @FindMe = 'simple'
Set @UseMeInstead = 'dull'
Select #1NormalReplace = Replace(@SearchMe, @FindMe, @UseMeInstead)
Set @SearchMe = 'This is a simple sentence.'
Set @FindMe = 'simple '
Set @UseMeInstead = ''
Select #2ReplaceWithEmpty = Replace(@SearchMe, @FindMe, @UseMeInstead)
Set @SearchMe = ''
Set @FindMe = 'simple '
Set @UseMeInstead = 'non-existent'
Select #3SearchTheEmptyString = Replace(@SearchMe, @FindMe, @UseMeInstead)
Set @SearchMe = 'This is a simple sentence.'
Set @FindMe = ''
Set @UseMeInstead = 'will not be used'
Select #4FindEmpty = Replace(@SearchMe, @FindMe, @UseMeInstead)
Results:
#1NormalReplace
--------------------------
This is a dull sentence.
(1 row(s) affected)
#2ReplaceWithEmpty
--------------------------
This is a sentence.
(1 row(s) affected)
#3SearchTheEmptyString
--------------------------
(1 row(s) affected)
#4FindEmpty
--------------------------
This is a simple sentence.
(1 row(s) affected)