QUOTENAME lengths

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    Comments posted to this topic are about the item QUOTENAME lengths

  • HappyGeek

    SSCoach

    Points: 18678

    Good question to start the week, thanks Steve.

    ...

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71808

    Nice, easy question to start the week on, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • morlindk

    SSCrazy

    Points: 2294

    Easy to answer. But actually a bit weird and impractical then, that the return type is nvarchar(258).

  • Thom A

    SSC Guru

    Points: 98641

    morlindk - Monday, October 8, 2018 1:45 AM

    Easy to answer. But actually a bit weird and impractical then, that the return type is nvarchar(258).

    This actually makes sense. QUOTENAME is really for quoting an object's name. Object names use the datatype sysname (which is a synonym for nvarchar(128)), and that is actually the expected input type for QUOTENAME too. 258 is 128 x 2 + 2, which means that it can handle every possible object name. The +2 handles the brackets ([]) at either end, and the x2 handles the escape characters. Thus, if someone was (foolish) enough to create an object with 128 ]'s, QUOTENAME would be able to return the correct value:

    SELECT QUOTENAME(REPLICATE(N']',128));
    Which returns a 256 ]'s enclosed by brackets ([]).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    Thom A - Monday, October 8, 2018 4:44 AM

    morlindk - Monday, October 8, 2018 1:45 AM

    Easy to answer. But actually a bit weird and impractical then, that the return type is nvarchar(258).

    This actually makes sense. QUOTENAME is really for quoting an object's name. Object names use the datatype sysname (which is a synonym for nvarchar(128)), and that is actually the expected input type for QUOTENAME too. 258 is 128 x 2 + 2, which means that it can handle every possible object name. The +2 handles the brackets ([]) at either end, and the x2 handles the escape characters. Thus, if someone was (foolish) enough to create an object with 128 ]'s, QUOTENAME would be able to return the correct value:

    SELECT QUOTENAME(REPLICATE(N']',128));
    Which returns a 256 ]'s enclosed by brackets ([]).

    +1
    Super explanation.  Thanks, Thom.

  • Shayn Thomas

    SSCertifiable

    Points: 5603

    Nice question and great additional info from Thom

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

Viewing 7 posts - 1 through 7 (of 7 total)

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