Quotename () function

I have read a lot of help to understand the quotename function, as shown in the following example.
Quotename (TRANSACT-SQL)

Returns a unicode string with delimiters. Adding delimiters can make the input string a valid Microsoft SQL Server 2005 separator.

Transact-SQL syntax conventions

Syntax

Quotename ('character _ string' [, 'quote _ character ']) parameter
'Character _ string'

A string consisting of Unicode characters. Character_string is the sysname value.

'Quote _ character'

A single character string used as a separator. It can be single quotation marks ('), left square brackets, right square brackets ([]), or double quotation marks ("). Square brackets are used if quote_character is not specified.

Return type
Nvarchar (258)

Example
The following example accepts the string ABC [] def and uses the [and] characters to create a valid SQL Server separator identifier.

Copy code
Select quotename ('abc [] def ') below is the result set:

However, I still cannot understand it. Can you give it a simple introduction.
========================================================== ======================================
First, the identifiers in SQL Server have certain rules, such as you
Create Table ABC123 (...)
There are spaces in the middle. It does not comply with the rules.

You will write it into create table [abc 123] (...)
[] Is used to define the identifier.

Quotename makes the string a valid identifier.

What is its use? Let me give you an example.

You have a table named AA [] BB.

How do you write dynamic statement queries for some applications?
Exec ('select * from AA [] BB ')
?

Set @ SQL = 'select * from' + quotename ('aa [] BB ')
Exec (@ SQL)

Of course, you can also write the escape Code directly.

Select * from [AA [] BB]

That is, quotename makes the input in the function a valid identifier.
For example, in the above example, AA [] BB is not a valid identifier.
========================================================== ======================================
Thank you for your advice. Here are the answers in msdn.
Example
The following example accepts the string ABC [] def and uses the [and] characters to create a valid SQL Server separator identifier.

Copy code
Select quotename ('abc [] def ') below is the result set:

Copy code
[ABC [] def]

(1 row (s) affected) note that there are two right brackets in the string ABC [] def to indicate escape characters.

I don't understand why

Select quotename ('abc [] def '). The result set is [ABC [] def].

Why is there an additional] symbol in the middle?
The result should be
[Abc [] def]
Is MSND an error?

========================================================== ======================================
If it is the result you wrote, it will be incorrect, because [] This is a pair !!!

Therefore, you must add an escape character to avoid errors!
========================================================== ======================================
Another point is that the quotename function can be written in several ways:
The valid identifier generated by quotename ('A') is [aa]
The valid identifier generated by quotename ('aaa', '') is [aa]
The valid identifier generated by quotename ('A', ''') is 'A'
========================================================== ======================================
To put it simply
For example, you have a table named index.
You have a dynamic query. The parameter is the table name.
Declare @ tbname varchar (256)
Set @ tbname = 'index'
--- Query the data in this table:
Print ('select * from' + @ tbname)
Exec ('select * from' + @ tbname)

-- The print data is
Select * from index

Because index is a key word, it must have an error. You can add brackets:
Select * from [index]

QUOTENAME is available, namely:
Print ('select * from' + QUOTENAME (@ tbname ))
-- Result: select * from [index]
Exec ('select * from' + QUOTENAME (@ tbname ))

========================================================== ======================================
Thank you very much. I have seen that this function is often used in row-to-column conversion. What do I think is the relationship with row-to-column conversion, it seems that the key factor for Row-to-column conversion is not in this function. Thank you very much for your guidance.
========================================================== ======================================