replicate

Question

replicate

VM-723206

SSCrazy

Points: 2964
More actions
October 7, 2009 at 11:45 pm

#205312

Comments posted to this topic are about the item replicate

Viewing 15 posts - 1 through 15 (of 15 total)

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

Chris Houghton SSCrazy Points: 2557 More actions · Answer 1

Good question. Demonstrates the requirement to explicitly cast a string value exceeding 8000 bytes as Varchar Max to prevent truncation. Thanks for the education.

munirdahya Grasshopper Points: 10 More actions · Answer 2

Cut and paste the code in QA and you get:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'max'.

Server: Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'max'.

Server: Msg 137, Level 15, State 1, Line 3

Must declare the variable '@str'.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Chris Houghton SSCrazy Points: 2557 More actions · Answer 3

It's a SQL Server 2005 or above question pal. It won't work on 2000. That wasn't very clear in the question though.

Bob Bobbity SSC Veteran Points: 210 More actions · Answer 4

If you'd given me the 18003 option, I'd have taken it

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 5

Bob Bobbity (10/8/2009)
If you'd given me the 18003 option, I'd have taken it

I must admit that would have been my favourite wrong answer if I was setting the question!

Wasn't sure why 16005 was there

Kev

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 6

Bob Bobbity (10/8/2009)
If you'd given me the 18003 option, I'd have taken it

Me too, I wouldn't have given it a second's thought. Good question!

Ron Moses

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Michael Poppers SSCrazy Points: 2150 More actions · Answer 7

Chris Houghton (10/8/2009)
It's a SQL Server 2005 or above question pal. It won't work on 2000. That wasn't very clear in the question though.

It was a good Q for me precisely because, until very recently, I was only working w/ SQL Server releases prior to v9 (SQL2005) and still had in my head that the max. size for a VARCHAR was 8000 bytes even as I had heard about VARCHAR(MAX), which I see (e.g. see http://teratrax.com/articles/varchar_max.html) is only available as of SQL2005.

SanjayAttray SSChampion Points: 13157 More actions · Answer 8

Good question. But the real googly would have been if 18003 was included in options in place of 8000 or 16005.

Then you would have seen more wrong answers than correct. 10000 + 8000 + 1 + 1 + 1 = 18003.

SQL DBA.

Joshua M Perry SSCrazy Points: 2675 More actions · Answer 9

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

I guess the documentation is wrong...it says truncation occurs only if you're NOT using varchar(max) or nvarchar(max) - they need to remove the NOT.

Joshua Perry
http://www.greenarrow.net

brdudley SSCrazy Points: 2320 More actions · Answer 10

The string being replicated must be a varchar(max) not the result string.

Select Len(Replicate( Cast('#' as varchar(max)), 10000)) -- 10,000

Select Len(Cast( Replicate( '#', 10000) as varchar(max) )) -- 8,000

Seems like an odd choice of convention unless it is for some backward compatability. Perhaps some people were relying on the truncation behavior.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 11

I've altered the question to say SQL 2005. However varchar(max) wasn't available before that version. We typically don't make up functions in the questions, only the answers 😉

Tom Garth SSCertifiable Points: 6193 More actions · Answer 12

Nice question. Hopefully I'll trip over this one enough times to prevent me from making that mistake when it counts.

Thanks,

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 13

Definitely, an interesting spin would have been:

declare @STR varchar(max)

select @STR = replicate('#',10000) + replicate(cast('#' as varchar(max)),10000)+ '#' + '#' + '#'

select len(@str)

To demonstrate the fact that replicate(cast('#' as varchar(max)),10000) yields a 10000 char long string

john.arnott SSChampion Points: 11882 More actions · Answer 14

Joshua Perry-333829 (10/8/2009)
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
I guess the documentation is wrong...it says truncation occurs only if you're NOT using varchar(max) or nvarchar(max) - they need to remove the NOT.

Nope. The doc is correct. When you code REPLICATE('#',10000) then the string expression '#' is seen as a single character, not a string of type varchar(max). The tricky part of the QOD was that even though the output of the function was being concatenated into a varchar(max), the string being repeated in that first REPLICATE was not a varchar(max), so its output is truncated to 8000. The second REPLICATE casts the '#' as varchar(max) and so the function output is not truncated.