Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 VarChar(max)?!? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, August 09, 2008 11:38 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 9:58 AM Points: 165, Visits: 294
 Comments posted to this topic are about the item VarChar(max)?!? PeteKI have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #549830
 Posted Sunday, August 10, 2008 8:58 PM
 SSCommitted Group: General Forum Members Last Login: Monday, December 09, 2013 8:43 AM Points: 1,568, Visits: 635
 I thought the explanation was really lacking something ... like explainingThe 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 QOTDSET @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:`80002000016000`
Post #549966
 Posted Monday, August 11, 2008 12:58 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 26, 2013 5:03 AM Points: 2,365, Visits: 1,830
 Yup... i got the same results.. "Keep Trying"
Post #550030
 Posted Monday, August 11, 2008 3:09 AM
 SSCertifiable Group: General Forum Members Last Login: Thursday, November 07, 2013 7:19 AM Points: 5,028, Visits: 1,363
 Nice question.
Post #550081
 Posted Monday, August 11, 2008 3:54 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 8:11 AM Points: 3,241, Visits: 4,974
 Nice... Atif Sheikh ----------------------------------------------------------------------------------------------------------------------------------------------------------------------Sometimes, winning is not an issue but trying.You can check my BLOG here
Post #550092
 Posted Monday, August 11, 2008 7:11 AM
 SSCoach Group: General Forum Members Last Login: Friday, November 01, 2013 1:55 PM Points: 15,442, Visits: 9,579
 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #550220
 Posted Monday, August 11, 2008 8:50 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 8:24 AM Points: 3,612, Visits: 3,535
 Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.
Post #550312
 Posted Monday, August 11, 2008 11:40 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, September 15, 2008 12:02 PM Points: 1,318, Visits: 57
 Nice question. This hasn't burned my yet and this type of question may mean it never does! Thanks.
Post #550483
 Posted Monday, August 11, 2008 2:40 PM
 SSC Eights! Group: General Forum Members Last Login: Friday, February 04, 2011 7:20 AM Points: 977, Visits: 1,499
 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 GarthVertical 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
Post #550613
 Posted Monday, August 11, 2008 4:07 PM
 SSCommitted Group: General Forum Members Last Login: Monday, May 13, 2013 1:51 PM Points: 1,946, Visits: 257
 this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
Post #550655

 Permissions