CASTing

  • Comments posted to this topic are about the item CASTing

  • Hi All..

    [Code]

    declare @C varchar(8000)

    set @C = N'hello'

    [/Code]

    In the above statement there is the "N" mentioned in the beginning of the string.. what does it mean exactly???

    Is it indirectly enforcing the casting of varchar into Nvarchar????

    Can any one explain??

    Thanks in advance..

    Ramkumar . K
    Senior Developer
    ######################
    No Surrender... No Give Up....
    ######################

  • Hi,

    You can find this information about the N-prefix in the BOL:

    Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. This is true even if the column being referenced is already defined as Unicode. Without the N prefix, the string is converted to the default code page of the database. This may not recognize certain characters.

    Hans

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Where does it say anything about 4000?

  • The answer says it's cast as nvarchar(4000) initially, but i too dont see how?

    Am i missing something, or is there a typo?

    Matt

  • Because of the N-prefix the value is cast to NVarChar. The NVarChar(MAX) can contain a maximum of 4000.

    BOL:

    nvarchar [ ( n | max ) ]

    Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Thankyou Hanshi.

    Ive always wondered what the N was for. Thanks.

    Matt.

  • Very good question 🙂

    Tests number of areas of knowledge .. what something logically represents vs. what is physically represents

    😎

  • Just to clarify HanShi's posting - NVARCHAR(MAX) is not limited to 4000 characters. The maximum length of VARCHAR(MAX) is 2^31 -1 (2,147,483,647) characters.

  • Uses per character 16 bits rather than 8.

  • 1. The "N" prefix indicates "National" characters, that is, double-byte characters to support large-alphabet languages such as Japanese.

    2. The concatenation of N'hello' to the repeated hyphens is where the first conversion to national characters happens.

    3. Although a variable defined as varchar(max) or nvarchar(max) may hold many more characters, without the "max", the largest size is 8000 bytes (4000 double-byte characters).

    4. The assignment of the concatenated string to @C converts the nvarchar data back to varchar, but it's already truncated at 4000 characters

    Try running the code with different values to see either the truncation in effect or parsing errors on the maximum size of a data type.

    Finishes with same output as original:

    declare @C varchar(7000)

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

    print len(@c)

    print @C

    Max size error:

    declare @C varchar(9000)

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

    print len(@c)

    print @C

  • About size of nvarchar(max)

    I tried this:

    declare @C nvarchar(max)

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

    print len(@c)

    the result was still 4000. Can someone explain why it wasn't more since nvarchar(max) can accept more then 4000

  • Aleksandr,

    The truncation happens in the concatenation of the hyphens to N'hello', step 2 in my previous post. It's truncated because the string 'hello' is cast as National characters, so the concatenation is kept as double-bytes with a limit of 4000. This is before the string is assigned to the local variable, so even though @C can hold more, the 4000 characters is all that is sent to it.

    Again, try it out.....

    declare @C nvarchar(max)

    set @C = Convert(nvarchar(max), N'hello') + replicate('-',9500)

    print len(@c)

    set @C = Convert(nvarchar(max), N'hello') + convert(nvarchar(max),replicate('-',9500))

    print len(@c)

    set @C = Convert(nvarchar(max), N'hello') + replicate(convert(nvarchar(max),'-'),9500)

    print len(@c)

    set @C = N'hello' + replicate(convert(nvarchar(max),'-'),9500)

    print len(@c)

  • Good Questions ............ 😛

  • Aleksandr Furman (5/27/2008)


    About size of nvarchar(max)

    I tried this:

    declare @C nvarchar(max)

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

    print len(@c)

    the result was still 4000. Can someone explain why it wasn't more since nvarchar(max) can accept more then 4000

    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.

Viewing 15 posts - 1 through 15 (of 20 total)

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