Is there a Bug with the Replace() Function in SQL Server 2005
-
You are comparing apples to oranges when comparing the action of REPLACE with UPPER. REPLACE doesn't know how big the returned string is going to be when the replacement is completed.
Example:
declare @teststr varchar(20)set @teststr = 'abcabcabcabcxyz';select replace(@teststr,'abc','abcd')Running the code above, the string value in @teststr becomes longer. After the replace above it is 4 characters longer. How is replace to know this in advance? UPPER, on the other hand, isn't changing the length of the string passed in, only converting any lower case characters to upper case. It knows how long the returned string is going to be based on the length of the input string.
-
So, once again I will ask the question. Given:
SELECT REPLACE('abcdefghijklmno', 'mno', 'abc');
What is the expected datatype of the output? VARCHAR - as defined in BOL.
What is the expected length of the output? 10, 20, 30 ...?
In the above sample, could we assume that REPLACE would return a VARCHAR(15)? If so, why do you think that is valid?
In this example, ask the same questions:
SELECT REPLACE(t.MyVarchar50Column, 'some text', 'some new text')
FROM dbo.MyTable AS t;
Should REPLACE return a VARCHAR(50)? Nope, can't do that or we get a truncation error.
Should REPLACE return a VARCHAR(60)? Nope, don't know that that would be large enough either.
Now, think about what it would take if you were to write your own replace function.
What are you going to define the return datatype as?
Are you going to define the return type as VARCHAR or NVARCHAR? Yes, of course you are.
Are you going to define the return datalength as 50? I don't think so.
Are you going to define the return datalength as 100? Nope, not that one either...
Are you going to define the return datalength as something dependent upon the input length?
I don't really see how you could do that, but for arguments sake - let's say that's exactly how you defined your custom replace function. Now, what happens when I do the following:
SELECT REPLACE('abc', 'c', 'd')
UNION ALL
SELECT REPLACE('abcd', 'c', 'd')
Oops - the first select defined the return type as VARCHAR(3) - the second select is therefore truncated.
Hmm, that means we have to define our replace function with a datalength that can handle all defined lengths of possible inputs. For VARCHAR that would be 8000 and NVARCHAR would be 4000. Since we cannot define a VARCHAR(9000) (or NVARCHAR(5000)) - the only other option is if we are on 2005 or greater and can use inputs defined as VARCHAR(max) - and only in that special situation where the input is defined as VARCHAR(max) - you can then return a VARCHAR(max).
So - this is definitely not a bug.
Could it be documented better? Not sure myself...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Lynn Pettis (12/18/2008)
You are comparing apples to oranges when comparing the action of REPLACE with UPPER. REPLACE doesn't know how big the returned string is going to be when the replacement is completed.Example:
declare @teststr varchar(20)set @teststr = 'abcabcabcabcxyz';select replace(@teststr,'abc','abcd')Running the code above, the string value in @teststr becomes longer. After the replace above it is 4 characters longer. How is replace to know this in advance? UPPER, on the other hand, isn't changing the length of the string passed in, only converting any lower case characters to upper case. It knows how long the returned string is going to be based on the length of the input string.
Your missing the point with the comparison. I'm not arguing that the function should know how long a string to retunr, only that if it is going to return the MAX length of that data type then it would be nice if they mentioned this in BOL.
Why mention it in BOL if it's the expected result?
Because there is no reason to expect or even assume this is how Replace will work. Give me one good reason why when desining this functions innner workings, the developer should choose to not trim away the excess spaces prior to returning the value? What possible benefit does including the extra spaces, possibly thousands of them, add to the function?
The answer is none.
Thanks for taking the time to respond.
Kindest Regards,
Just say No to Facebook! -
Jeffrey Williams (12/18/2008)
So, once again I will ask the question. Given:SELECT REPLACE('abcdefghijklmno', 'mno', 'abc');
What is the expected datatype of the output? VARCHAR - as defined in BOL.
What is the expected length of the output? 10, 20, 30 ...?
ANSWER: Len(@ValueReturnedByFunction)
In the above sample, could we assume that REPLACE would return a VARCHAR(15)? If so, why do you think that is valid?
Why would you think it's valid to always return a value padded with extra spaces? What benefit to a user of the function, does including these extra spaces provide?
Answer: It doesn't.
In this example, ask the same questions:
SELECT REPLACE(t.MyVarchar50Column, 'some text', 'some new text')
FROM dbo.MyTable AS t;
Should REPLACE return a VARCHAR(50)? Nope, can't do that or we get a truncation error.
Should REPLACE return a VARCHAR(60)? Nope, don't know that that would be large enough either.
Why should one have to tell Replace() how long a value to return? When we use the other String functions do we have to tell each of them how long a value to return? Of course not. My prior question still applies, why choose to return a value with spaces padded verses removing those extra spaces just prior to returning the value?
Now, think about what it would take if you were to write your own replace function.
What are you going to define the return datatype as?
Are you going to define the return type as VARCHAR or NVARCHAR? Yes, of course you are.
Are you going to define the return datalength as 50? I don't think so.
Are you going to define the return datalength as 100? Nope, not that one either...
Are you going to define the return datalength as something dependent upon the input length?
Why are you so hung on requiring the function to return a pre-defined length of text? The whole point of VARCHAR is to have a variable length value. If Replace() is always going to return the max number of characters, using spaces padded to the right to ensure the length of the value is equal to the max then why go with the VARCHAR data type at all and not just stick with CHAR?
I don't really see how you could do that, but for arguments sake - let's say that's exactly how you defined your custom replace function. Now, what happens when I do the following:
SELECT REPLACE('abc', 'c', 'd')
UNION ALL
SELECT REPLACE('abcd', 'c', 'd')
Oops - the first select defined the return type as VARCHAR(3) - the second select is therefore truncated.
Hmm, that means we have to define our replace function with a datalength that can handle all defined lengths of possible inputs. For VARCHAR that would be 8000 and NVARCHAR would be 4000. Since we cannot define a VARCHAR(9000) (or NVARCHAR(5000)) - the only other option is if we are on 2005 or greater and can use inputs defined as VARCHAR(max) - and only in that special situation where the input is defined as VARCHAR(max) - you can then return a VARCHAR(max).
So - this is definitely not a bug.
Could it be documented better? Not sure myself...
I do appreciate your taking the time to reply but I think you are missing the point. Regardless of how the function should work in terms of what value it returns, because the data type is explcitly listed in BOL as VARCHAR, with no numerical value specified for the length then if it is going to always pad the returned value with spaces then it should say this in the documentation.
And NO it's not expected for Replace() to always work this way because there is no common benefit to including these extra spaces as opposed to trimming them away prior to returning the value. Note that I said COMMON benefit because there probably is some out of the norm scenario where this would be of a benefit however under common use the inclusion of the spaces offers no benefit.
I'll be sure to post a reply back when I hear back from Microsoft about my reporting this bug to them so we all know what their official stance is.
Thanks again for replying
Kindest Regards,
Just say No to Facebook! -
And you seem to be missing the point as well. When using REPLACE to create a table using SELECT INTO, how does the function know how long the longest returned string will be if you are generating a 1,000,000 row table? It doesn't. SQL Server has to create the table, which means it has to define that column to some length, so it defaults to the maximum length of the data type (varchar, 8000; nvarchar, 4000).
If you feel this needs to be detailed in BOL, go to Microsoft Connect and post it there. Maybe they will make the change, maybe not.
-
YSLGuru (12/15/2008)
I think I may have come across a bug with the Replace() function in SQL Server 2005.For what it's worth, I agree... if you compare the way the very closely related STUFF function works, it would appear that the way REPLACE operates is a bit of an anomoly.
Merry Christmas, folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lynn Pettis (12/17/2008)
since it needs to create the table on the fly it doesn't know how big the column needs to be so it so it makes it the maximum sizesort of makes sense, if you dont specify the array size it assumes the max.
logically, what else can the software do. Any thoughts?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Perry Whittle (12/25/2008)
Lynn Pettis (12/17/2008)
since it needs to create the table on the fly it doesn't know how big the column needs to be so it so it makes it the maximum sizesort of makes sense, if you dont specify the array size it assumes the max.
logically, what else can the software do. Any thoughts?
Yeah... look at the very closely related STUFF function and tell me why they couldn't make REPLACE work the same way? I agree... dunno if you actually want to call it a bug, but it's not consistent with STUFF.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How about a logical explanation? REPLACE is changing an unknown number of characters in the target string(s) whereas STUFF is adding a known number of characters at a specified location in the target string(s).
-
Refer
MadhivananFailing to plan is Planning to fail
-
Madhivanan (12/26/2008)
ReferYep... we all agree on that. I believe the main complaint is... the documentation doesn't say anything about the default size.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/26/2008)
Madhivanan (12/26/2008)
ReferYep... we all agree on that. I believe the main complaint is... the documentation doesn't say anything about the default size.
THANK YOU Jeff!
I was starting to wonder if I was just crazy because I was unable to articulate that the problem isn't necessarily that the function was not working like it should but that it was not in sync with it's official description and therefore either it is not working correctly or it is not described correctly.
Personally I believe the error is in the functions return type only because the more logical choice for a return type is a sting of text without any extra spaces. The function may not know in advance the length of the string passed to it or even how long the resulting string will be after all the replacements are done but it most certainly can and should know the length after the replacements are done and prior to returning the value to the code that called the function.
If there were a logical reason for why someone would want (for a majority of the time the function is used) the value returned by Replace() to be padded with spaces so that it is 8,000 characters in length then I would say BOL needs correcting but I can't see this as ever being the case let alone a majority of the time the function is used.
Thanks Again Jeff, your posts are always beneficial.
Kindest Regards,
Just say No to Facebook! -
Madhivanan (12/26/2008)
ReferThanks for providing a secondary referece to this being at least something to be informed of (because it is not the norm or not what is expected) if not an actual formal bug.
Kindest Regards,
Just say No to Facebook! -
It is a problem in the documentation only, not the function.
-
Heh... that's what's cool about discussions on forums... I get to disagree and not hurt anyone's feelings.
Personally, I'd prefer to see the function be a little smarter and, yeah, the data type should absolutely be correctly defined in the documentation even if they don't ever make it smarter.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply