CASTing

• Christian Buettner-167247

  SSChampion

  Points: 13729

  More actions

  May 27, 2008 at 3:09 pm

  #820857

  Simon Facer (5/27/2008)

  ---

  The REPLICATE('-', 8000) has an implicit cast to NVARCHAR

  Sorry Simon, this is not correct (or at least misleading).

  The type conversion happens during the concatenation of the two strings.

  -- Example 1:

  -- Replicating a non-unicode string results in a non-unicode string

  -- (Datalength = Lenght)

  SELECT DATALENGTH(REPLICATE('-', 8000)), Len(REPLICATE('-', 8000))

  -- Example 2: Concatenating a unicode string and

  -- a non-unicode string results in a unicode string

  -- (Datalength = 2* Lenght)

  SELECT DATALENGTH(N'Unicode' + 'Nonunicode'), LEN(N'Unicode' + 'Nonunicode')

  Best Regards,

  Chris Büttner

• Aleksandr Furman

  Ten Centuries

  Points: 1217

  More actions

  May 27, 2008 at 3:27 pm

  #820862

  Simon Facer (5/27/2008)

  ---

  The REPLICATE('-', 8000) has an implicit cast to NVARCHAR, try this to explicitly CAST it to NVARCHAR(MAX):

  declare @C nvarchar(max)

  set @C = N'hello' + CAST(replicate('-',8000) AS NVARCHAR(MAX))

  print len(@c)

  you get 8005 as the length.

  What I found very interesting is that to REPLICATE more then 8000 characters you have to cast inside value:

  convert(nvarchar(max),replicate('-',9500)) - This gets you 8000 characters

  vs.

  replicate(convert(nvarchar(max),'-'),9500) - This gets you 9500 characters

  Thank you John

• Simon Facer

  SSCertifiable

  Points: 5427

  More actions

  May 27, 2008 at 5:23 pm

  #820893

  Christian Buettner (5/27/2008)...Sorry Simon, this is not correct (or at least misleading)...

  Sorry, it could be construed as misleading. My assumption was that everyone would understand that the discussion was about NVARCHAR fields, seeing as that was the focus of the QOTD. My bad... :ermm:

• gurmeet.singh

  SSC Journeyman

  Points: 86

  More actions

  May 27, 2008 at 10:48 pm

  #820963

  [font="Verdana"]

  Some More Clarifications related to Varchar And NVarchar

  VARCHAR supports variable length strings, up to 8,000 characters. Best used when data length is variable, e.g. last names or product SKU codes.

  NVARCHAR Similar to VARCHAR, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is 4,000 characters).[/font]

  Good Question .....

• Peter E. Kierstead

  SSCarpal Tunnel

  Points: 4114

  More actions

  May 28, 2008 at 8:08 am

  #821191

  The answer to this question is technically correct...

  While Len(@c) is in fact 4000 the type and maximum len of @C is VarChar, 8000.

  Try concatenating another 4000 VarChar characters to @C and then look at the metrics!

  declare @C varchar(8000)

  set @C = N'hello' + replicate('-',8000)

  print len(@c)

  print @C

  set @C=@c+replicate('*',4000)

  print len(@c)

  print @C

  The leading type of the R-value (right-hand side of the assignment operator) is nVarChar, therefore then entire R-value was cast into this type, hence the max len 4000.

  ---

  
  PeteK
  I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  December 4, 2012 at 12:33 am

  #1565140

  Good question...

  got to know about use of prefix N...

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/