• 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

  • 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


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

    Thank you John

  • 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:

  • [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 .....

  • 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.

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

  • Good question...

    got to know about use of prefix N...

    To get quick answer follow this link:

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply