SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VarChar(max)?!?


VarChar(max)?!?

Author
Message
Peter E. Kierstead
Peter E. Kierstead
SSC Eights!
SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)

Group: General Forum Members
Points: 888 Visits: 453
Comments posted to this topic are about the item VarChar(max)?!?



PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Simon Facer
Simon Facer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 724
I thought the explanation was really lacking something ... like explaining

The REPLICATE function only returns 8000 characters (defaults to VARCHAR(8000)) if the character value to be replicated isn't explicitly CAST as VARCHAR(MAX). As both REPLICATE statements are internally CAST as VARCHAR(8000) fields, the concatenation of the two VARCHAR(8000) fields yields another VARCHAR(8000) field before assigning the value to the @str field, hence the results is 8000, not 16000 as you might expect.

To try and make that clearer ...
DECLARE @Str VARCHAR(MAX);

-- The Example from the QOTD
SET @Str=REPLICATE('*',10000) + REPLICATE('*',10000);
PRINT LEN(@Str);

-- Explicitly CAST the Replicated character as type VARCHAR(MAX)
SET @Str=REPLICATE(CAST('*' AS VARCHAR(MAX)),10000) + REPLICATE(CAST('*' AS VARCHAR(MAX)),10000);
PRINT LEN(@Str);

-- Explicitly CAST the results from REPLICATE statements as VARCHAR(MAX), without casting the Replicated character as VARCHAR(MAX)
SET @Str=CAST(REPLICATE('*',10000) AS VARCHAR(MAX)) + CAST(REPLICATE('*',10000) AS VARCHAR(MAX));
PRINT LEN(@Str);



Gives the results:
8000
20000
16000





ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6781 Visits: 1865
Yup... i got the same results..

"Keep Trying"
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9843 Visits: 1407
Nice question.



Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5122 Visits: 5204
Nice...


Atif Sheikh

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

Sometimes, winning is not an issue but trying.

You can check my BLOG here


GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58717 Visits: 9730
I spotted the implicit conversion and was about to go into the details on it, but then realized Simon had already covered it.

Good question. Understanding implicit conversions is a critical skill for anyone dealing with code.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5225 Visits: 3648
Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.
TDuffy
TDuffy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1958 Visits: 57
Nice question. This hasn't burned my yet and this type of question may mean it never does! Thanks.



Tom Garth
Tom Garth
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2011 Visits: 1499
Even though the answer was obvious, and even though I had QA open at the time, I went ahead and "Took the Bait". What's 1 lousy point.

Good QotD. Learnt sumpin.

Tom Garth
Vertical Solutions

"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

vlad-548036
vlad-548036
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2970 Visits: 273
this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search