|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, March 25, 2013 8:37 AM
Points: 163,
Visits: 275
|
|
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.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:30 AM
Points: 1,566,
Visits: 600
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
|
|
Yup... i got the same results..
"Keep Trying"
|
|
|
|
|
SSCarpal Tunnel
       
Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789,
Visits: 1,336
|
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240,
Visits: 4,960
|
|
Nice...
Atif Sheikh
---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sometimes, winning is not an issue but trying. You can check my BLOG here
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
|
|
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
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 3,400,
Visits: 3,411
|
|
Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.
|
|
|
|
|
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.
|
|
|
|
|
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 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
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801,
Visits: 257
|
|
| this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
|
|
|
|