Quoting Identifiers

Question

Quoting Identifiers

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
November 2, 2018 at 12:19 am

#100987

Comments posted to this topic are about the item Quoting Identifiers

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

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

Bruceo SSCrazy Points: 2152 More actions · Answer 1

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: 182882 More actions · Answer 2

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 More actions · Answer 3

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: 3323 More actions · Answer 4

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: 5863 More actions · Answer 5

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: 31720 More actions · Answer 6

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: 5533 More actions · Answer 7

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: 182882 More actions · Answer 8

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: 72499 More actions · Answer 9

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: 16430 More actions · Answer 10

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 11

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR