Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

VarChar(max)?!? Expand / Collapse
Author
Message
Posted Saturday, August 9, 2008 11:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:22 PM
Points: 190, Visits: 436
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.
Post #549830
Posted Sunday, August 10, 2008 8:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 1,570, Visits: 677
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




Post #549966
Posted Monday, August 11, 2008 12:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 2,365, Visits: 1,846
Yup... i got the same results..

"Keep Trying"
Post #550030
Posted Monday, August 11, 2008 3:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Nice question.


Post #550081
Posted Monday, August 11, 2008 3:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:48 AM
Points: 3,241, Visits: 5,002
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #550220
Posted Monday, August 11, 2008 8:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 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
Post #550613
Posted Monday, August 11, 2008 4:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:59 PM
Points: 2,213, Visits: 266
this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
Post #550655
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse