Quoting Identifiers

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719736

    Comments posted to this topic are about the item Quoting Identifiers

  • Bruceo

    SSCrazy

    Points: 2152

    Steve Jones - SSC Editor - Friday, November 2, 2018 12:19 AM

    Comments posted to this topic are about the item Quoting Identifiers

    Hello Steve, is this from SQL2017 onward? 
    As far as I am aware the options were a lot less for 16

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Bruceo - Friday, November 2, 2018 1:10 AM

    Steve Jones - SSC Editor - Friday, November 2, 2018 12:19 AM

    Comments posted to this topic are about the item Quoting Identifiers

    Hello Steve, is this from SQL2017 onward? 
    As far as I am aware the options were a lot less for 16

    Hasn't changed from SQL Server 2008
    😎

  • Bruceo

    SSCrazy

    Points: 2152

    Eirikur Eiriksson - Friday, November 2, 2018 2:04 AM

    Bruceo - Friday, November 2, 2018 1:10 AM

    Steve Jones - SSC Editor - Friday, November 2, 2018 12:19 AM

    Comments posted to this topic are about the item Quoting Identifiers

    Hello Steve, is this from SQL2017 onward? 
    As far as I am aware the options were a lot less for 16

    Hasn't changed from SQL Server 2008
    😎

    My bad! Thanks for clearing that up. BOL confused me on this, must be missing something..

  • KS

    Hall of Fame

    Points: 3320

    Though I chose the option that has been marked correct, think the more appropriate answer would be "None of these. QUOTENAME() takes only one parameter" as the second parameter accepted is a one character string and the first 4 options are specified in plural which would make them incorrect.

    Apparently learnt something new, that no need to overthink πŸ˜‰

    P.S - No offence, Pun Intended πŸ™‚

  • Shayn Thomas

    SSCertifiable

    Points: 5603

    nice and easy one to end the week,
    cheers steve

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

  • webrunner

    SSC-Dedicated

    Points: 30254

    Thanks for this instructive question!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Ray Herring

    SSCertifiable

    Points: 5517

    Eirikur Eiriksson - Friday, November 2, 2018 2:04 AM

    Bruceo - Friday, November 2, 2018 1:10 AM

    Steve Jones - SSC Editor - Friday, November 2, 2018 12:19 AM

    Comments posted to this topic are about the item Quoting Identifiers

    Hello Steve, is this from SQL2017 onward? 
    As far as I am aware the options were a lot less for 16

    Hasn't changed from SQL Server 2008
    😎

    From SQL 2008 BOL
    <quote>
    ' quote_character '
    Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.
    </quote>

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Sowbhari - Friday, November 2, 2018 3:59 AM

    Though I chose the option that has been marked correct, think the more appropriate answer would be "None of these. QUOTENAME() takes only one parameter" as the second parameter accepted is a one character string and the first 4 options are specified in plural which would make them incorrect.

    Apparently learnt something new, that no need to overthink πŸ˜‰

    P.S - No offence, Pun Intended πŸ™‚

    He he, quotename of course has several secrete non-documented parameters, one of which would be ones ancestor level and family tree branch as a two byte binary value πŸ˜€
    😎
    If I run SELECT QUOTENAME('Eirikur',0x0F','P') I'll get 'Eirikur Raudi', Eric the Red
    Disclaimer: Results will vary depending on nationality, version and edition. This will only work on the "God Edition" of SQL Server
     

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71808

    Nice one, thanks Steve.

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    Sowbhari - Friday, November 2, 2018 3:59 AM

    Though I chose the option that has beenmarked correct, think the more appropriate answer would be "None of these. QUOTENAME() takes only one parameter" as the second parameter accepted is a one character string and the first 4 options are specified in plural which would make them incorrect.

    I would say that the current "correct" answer is still valid given that the second parameter does at least accept multiple characters, even if it is only the first one that counts:

    SELECT QUOTENAME('a', '""'); -- "a"
    SELECT QUOTENAME('a', '[]'); -- [a]
    SELECT QUOTENAME('a', '{}'); -- {a}

    And just to show even more clearly how much any character beyond the first (for the 2nd parameter) doesn't matter:

    SELECT QUOTENAME('a', '"ZZZZ'); -- "a"

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky

    SSCoach

    Points: 16251

    Ray Herring - Friday, November 2, 2018 8:31 AM

    Eirikur Eiriksson - Friday, November 2, 2018 2:04 AM

    Bruceo - Friday, November 2, 2018 1:10 AM

    Steve Jones - SSC Editor - Friday, November 2, 2018 12:19 AM

    Comments posted to this topic are about the item Quoting Identifiers

    Hello Steve, is this from SQL2017 onward? 
    As far as I am aware the options were a lot less for 16

    Hasn't changed from SQL Server 2008
    😎

    From SQL 2008 BOL
    <quote>
    ' quote_character '
    Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.
    </quote>

    You need to be very careful when quoting the documentation: it is not necessarily correct. This here quote is a good example. I ran the following query on SQL Server 2005 (SP4 or whatever the last SP was):

    ;WITH cte AS
    (
    SELECT TOP(224) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 31 AS [num]
    FROM master.sys.all_objects obj
    )
    SELECT cte.[num] AS [Value],
           CHAR(cte.[num]) AS [Character],
           QUOTENAME('a', CHAR(cte.[num])) AS [QuoteName]
    FROM cte
    WHERE QUOTENAME('a', CHAR(cte.[num])) IS NOT NULL
    ORDER BY cte.[num];

    And that returned the following result:

    Value  Character  QuoteName
    34     "          "a"
    39     '          'a'
    40     (          (a)
    41     )          (a)
    60     <          <a>
    62     >          <a>
    91     [          [a]
    93     ]          [a]
    96     `          `a`
    123    {          {a}
    125    }          {a}

     
    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

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

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