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 15 posts - 31 through 45 (of 50 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Give me time to eat dinner, check a failed process at work, and then I'll be more than happy to explain from a logical view point why I don't think it's a bug in the function.

What good is adiscussion if you don't provide something to back up your belief? Should have earlier any way, but had other things to do at work when I made the quick post (hate ad hoc called meetings, they just get in the way!).

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

Heh... I've already seen all of those logical discussions above. Although very well founded, I still just happen to disagree with them.

--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 SSC Guru Points: 442467 More actions · Answer 3

Okay, but why? I look at it this way, if I'm willing to put down my reasoning, I think it only right to ask you to support your side of the discussion as well. That's what makes a discussion worthwhile, see how the other side thinks. We may finally just agree to disagree on this subject, which is just as good an outcome as one of us convincing the other that their argument makes sense.

Ready?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

Sorry... getting a bit tired and, like you said, shouldn't have posted "the short answer". 😀

What happens with the following in SQL Server 2000? 2005?

SELECT STUFF(REPLICATE('=',8000),1,0,'=')

--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 SSC Guru Points: 442467 More actions · Answer 5

Jeff Moden (1/14/2009)
Sorry... getting a bit tired and, like you said, shouldn't have posted "the short answer". 😀
What happens with the following in SQL Server 2000? 2005?
SELECT STUFF(REPLICATE('=',8000),1,0,'=')

Without testing it, a warning regarding truncation or a failure. However, if you had the following:

SELECT STUFF(REPLICATE('=',7999),1,0,'=') SQL Server whould know that the return value would be 8000 characters. Your input strng is 7999 characters and you are adding 1 character without deleting any.

I'll take your example a little further in my next post (I am actually writing another one in another tab in IE).

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Test it... you'll find out differently... no warning.

Same holds true for the following...

SELECT REPLACE(REPLICATE('=',7999)+'9','9','88')

Truncation with no warning.

So, there is truncation with no warning for both. To me, that means that having one expand to the trimmed length and the other defaulting to a VARCHAR(8000) is pretty bogus. Regardless of whether one inserts a known number of characters or not, they should both work the same way because they can both violate the 8 k barrier, cause truncation, and neither will warn you that the truncation has occurred.

--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 SSC Guru Points: 442467 More actions · Answer 7

I would call the truncation without warning a possible bug, but I think we have seen that behaviour else where in SQL Server as well. The fact that REPLACE defaults to 8000 bytes (nvarchar(4000) if one of the inputs is nvarchar, or varchar(8000) if none are nvarchar).

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

Heh... it actually doesn't really matter at all... here's why...

SELECT TOP 1000

REPLACE(REPLICATE('=',t.N)+'9','9','88') AS R,

STUFF(REPLICATE('=',t.N)+'9',1,0,'88') AS S

INTO #MyHead

FROM dbo.Tally t

EXEC sp_Help 'TempDB..#MyHead'

I'm pooped... gotta be up at 5:30... I'm goin' to bed. We can continue this tomorrow night... a little earlier.

--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 SSC Guru Points: 442467 More actions · Answer 9

Okay, I'm ready for my logical look at why it isn't a bug in the REPLACE function. First, let us think in terms of your million row test tables, so we have 1,000,000 rows of data that we are going to apply the REPLACE and STUFF functions to a single column in the test table.

At this time, let us keep the discussion theoretical, we can always go to code later if needed, but some of the descriptions make sense to write in SQL.

Base assumptions for this discussion, starting with what I mentioned above.

The table:

create table dbo.StringTest1 (

StringTestID int not null identity(1,1),

StringData varchar(100) -- each row in the table can have 0 to 100 characters

);

As above, assume that the table has 1,000,000 rows.

The data in each row consists of random characters from A to Z, and each character may appear in each row 0 to 100 times.

We are going to create a new table, dbo.StringTest2, using the following SQL code:

select

StringTestID,

REPLACE(StringData, 'A', 'BIZZBUZZ') as StringData1,

STUFF(StringData, 1, 0, 'BIZZBUZZ') as StringData2

into

dbo.StringTest2

from

dbo.StringTest1;

When this statement is run, SQL Server needs to create the table dbo.StringTest2. How is it going to define the columns StringData1 and StringData2? A simple test using a variable defined as varchar(100) in the above select statement creates a table where StringData1 is varchar(8000) and StringData2 is varchar(108), and this with an actual value of 'A' in the variable. If you change the value of the variable to 'B', StringData1 is still defined as varchar(8000).

Why do you suppose it does this? The only way it could know how large to make StringData1 would be to perform the REPLACE first, then create the column. But if the input is the value of a specified column in a table, and that table has 1,000,000 rows then SQL would have to process 1,000,000 rows to determine the final size of the column before it creates the table. However, the table needs to exist before you can insert the data. Now you have a Catch-22.

How do you get around that, you set a default size for the output, which in this case is 8000 bytes (nvarchar(4000) or varchar(8000)).

I don't see it as a bug, but as a design decision. Could the documentation be better, most definitely.

Doesn't matter to me if I convice you or not. We have had a good discussion, and we have listened to each others side of the coin. If it should land on its edge, than that is the way it is.

I will respect your opinion as I know you will mine, as we are both professionals (or at least I think I am).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Different subject, have you had a chance to test my code yourself? After my last test, I feel much better.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

Heh... beat you to it. See you tomorrow night.

--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 SSC Guru Points: 1004638 More actions · Answer 12

Lynn Pettis (1/14/2009)
Different subject, have you had a chance to test my code yourself? After my last test, I feel much better.

No... and today pretty much stunk for doing anything except a couple of after-dinner posts... black ice on the way home... took 2:45 to get home and THEN had to run errands. Had a guy spin out right in front of me in a Jeep Grand C... dummy didn't know that if ticked while driving in slow traffic and you floor a 4 week drive on black ice on a curve, all wheels loose traction at the same time. He went down a 15 foot embankment... I couldn't help it... I laughed out loud at the sheer stupidity of it all.

--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 SSC Guru Points: 442467 More actions · Answer 13

Only because I kept reading and responding to your posts while still trying to write my first post. Sorry, don't know how to do both at the same time with one keyboard and monitor.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (1/14/2009)
Lynn Pettis (1/14/2009)
Different subject, have you had a chance to test my code yourself? After my last test, I feel much better.
No... and today pretty much stunk for doing anything except a couple of after-dinner posts... black ice on the way home... took 2:45 to get home and THEN had to run errands. Had a guy spin out right in front of me in a Jeep Grand C... dummy didn't know that if ticked while driving in slow traffic and you floor a 4 week drive on black ice on a curve, all wheels loose traction at the same time. He went down a 15 foot embankment... I couldn't help it... I laughed out loud at the sheer stupidity of it all.

LOL!!!

When my oldest daughter was still driving with a permit, she watch a truck go airborne and flip in front of her. My wife turned from looking back over her shoulder to see the truck upside down in the air. Oh, and it was coming at them across a divided 6 lane road (3 each way).

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 15

Okay, I have read through all of this - and I have to say I agree with Lynn. In fact, this is what I was trying to get at in the first place.

It really comes down to meta data - with STUFF we have all of the information needed to define the size of the output column, using just the query and meta data. With REPLACE - that can only be known once every column in the resultset has been processed and the replace function applied to the data.

I don't see any other way it could be done.

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