QUOTENAME SQL Server Function By Practical Example

Summary: in this SQL tutorial, you will learn when/why would you use QUOTENAME? and how to use the QUOTENAME function in (t-SQL) SQL Server to make a string a valid SQL Server delimited identifier.

QUOTENAME() SQL Server Function

The QUOTENAME() function in SQL Server, returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
In another word you can say that the QUOTENAME() function adds delimiters to an input string to make that string a valid SQL Server delimited identifier.
It was basically, designed for quoting databases and their objects.
The function accepts two arguments; the input string (required), and a delimiter character (optional).

Syntax:

The syntax goes such as this:

In this syntax:
These function accepts two arguments:

  • Where character_string is the input string, which is required and quote_character is an optional argument to specify a character that uses as the delimiter. If omitted, brackets are used as the delimiter.
.
Important Note: character_string is a sysname whose maximum length is 128. if the length of the Inputs greater than 128 characters the function will return NULL.

For the optional quote_character argument Valid delimiters are:

  • ` (backtick)
  • ' (single quotation mark)
  • " (double quotation mark)
  • [ ] (left or right bracket)
  • ( ) (left or right paranthesis)
  • < > (less than or greater than sign)
  • { } (left or right brace)
.
Note:Important Note: Therefore, if you use an invalid character, then the function will return NULL. The quote_character defaults to brackets if you skip it.
The SQL Server QUOTENAME() Function is very useful in dynamic SQL.

Applies To:

SQL Server (starting with 2008), Azure SQL Database, Azure Synapse Analytics, Azure SQL Managed Instance, Parallel Data Warehouse

QUOTENAME() SQL Server Function Example

Example:2

The given below following statements create a new table such as student details whose name contains a space and insert a new row into the table:

The given below following SQL code attempts to query data from the [student details] table using dynamic SQL:


There are the following code returns the following error:
Invalid object name 'student'.
QUOTENAME SQL Server Function Example

The given below following code makes the table name valid by using the SQL Server QUOTENAME() function:


QUOTENAME function output

Question: When/why would you use QUOTENAME ?

Answer: Additionally, the SQL-99 standard is to quote by using a single quote character, and whereas current versions of SQL Server still to use brackets, it’s going to within the future use the SQL-99 standard. within which case, all code using QUOTENAME will continue to function correctly, whereas code that tries to do it’s own escaping will fail.

Since the QUOTENAME SQL Server function has the exact same limitations as sysname, should Microsoft ever decide to change sysname to be longer than 128 characters (256 maybe? 32767 maybe?), it’s assumed that the QUOTENAME SQL Server Function would then even be ready to handle these increased sizes.

Using QUOTENAME is a safe manner of taking a column-name from a possibly untrusted source and using it such as a sysname – no matter the future/current database settings without having to worry about the edge cases (like ] or ‘ inside the input) and whether it’ll permit the string to break out of the column name to create SQL injection attacks. I probably would not depend entirely on this feature for security, however, to be utilized in one in all several layers of protection.

Conclusion:

you have learned when/why would you use QUOTENAME? and how to use the QUOTENAME function in (t-SQL) SQL Server to make a string a valid SQL Server delimited identifier.