Is there a Bug with the Replace() Function in SQL Server 2005

Question

Is there a Bug with the Replace() Function in SQL Server 2005

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

jim.riedemann SSChasing Mays Points: 650 More actions · Answer 1

The length increase is not coming from REPLACE(), it's coming from the SELECT statement. If you run the following PRINT statement you will see that the output is only the original 6 characters you started with.

PRINT LEN(REPLACE('abcdef', 'cde', 'xxx'))

SELECT, however, needs more guidance on what data type the output column should be. Since the expression is not originating with a table, it has to guess on the resultant data type. Since REPLACE() can output a VARCHAR(8000) SELECT has no choice but to allow for this. Since you are displaying results to text or file, the only way to line it up is to use the full 8000 characters.

I would use CAST/CONVERT to force the data type right in the SELECT statement.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

jim.riedemann (1/16/2009)
The length increase is not coming from REPLACE(), it's coming from the SELECT statement. If you run the following PRINT statement you will see that the output is only the original 6 characters you started with.
PRINT LEN(REPLACE('abcdef', 'cde', 'xxx'))
SELECT, however, needs more guidance on what data type the output column should be. Since the expression is not originating with a table, it has to guess on the resultant data type. Since REPLACE() can output a VARCHAR(8000) SELECT has no choice but to allow for this. Since you are displaying results to text or file, the only way to line it up is to use the full 8000 characters.
I would use CAST/CONVERT to force the data type right in the SELECT statement.

Now, that's interesting... and, just to be sure, I used DATALENGTH to see if there were any trailing spaces... none! Thanks for the tip. Certainly puts the kabosh on the whole basis of the thread.

--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)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

Thanks to all who have replied, especially those in disagreement with my stance and others because you've made your case in a professional manner. If only the rest of the internet community acted this way.

Even though there are 2 distinct sides on this matter of this being a Bug in the Replace() function or something By Design, I do believe we are all in agreement that no matter how we think it should work, BOL (Books On Line) does not properly detail how Replace() works as far as what exactly the function returns, at least when the value passed is of type VARCHAR().

If anyone is in disagreement with about BOL needing revising on this point, please chime in.

Thanks again.

Kindest Regards,

Just say No to Facebook!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Jeff Moden (1/14/2009)
you floor a 4 week drive on black ice on a curve, all wheels loose traction at the same time.

that's neat. On the flip side, due to the 60\40 split and its power, my subaru will kick the rear out and over steer 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 5

YSLGuru (1/27/2009)
Even though there are 2 distinct sides on this matter of this being a Bug in the Replace() function or something By Design, I do believe we are all in agreement that no matter how we think it should work, BOL (Books On Line) does not properly detail how Replace() works as far as what exactly the function returns, at least when the value passed is of type VARCHAR().

I agree that BOL is not as clear as it could be on the length of function results.

Sometimes, BOL makes the return length explicit - e.g. QUOTENAME returns nvarchar(258) http://msdn.microsoft.com/en-us/library/ms176114.aspx

But in other cases they seem to assume either the same size (e.g. UPPER) or a calculated/supplied size (e.g. STUFF).

The one function that this doesn't work for is REPLACE(), which makes multiple substitutions.

If BOL were explicit on return lengths, especially for REPLACE(), it would avoid some confusion.

As an aside, the maximum length of REPLACE() can be calculated - it is something like:

LEN(REPLACE(a,b,c)) <= (LEN(a)/LEN(b))*(LEN(c) - LEN(b))

That is, the number of times that b can occur in a (LEN(a)/LEN(b) - truncated to integer),

multiplied by the increase in length when b is replaced by c (LEN(c) - LEN(b)).

But maybe that's too much to implement - a default of 8000 is a lot easier to specify 🙂