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:

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

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.

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)

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

SELECT QUOTENAME('what''s is your city?')      -- Returns [what's is your city?]

SELECT QUOTENAME('what''s is your city?', '[') -- Returns [what's is your city?]
SELECT QUOTENAME('what''s is your city?', ']') -- Returns [what's is your city?]

SELECT QUOTENAME('what''s is your city?', '''') -- Returns 'what''s is your city?'
SELECT QUOTENAME('what''s is your city?', '"') -- Returns "what's is your city?"

SELECT QUOTENAME('what''s is your city?', ')') -- Returns (what's is your city?)
SELECT QUOTENAME('what''s is your city?', '(') -- Returns (what's is your city?)

SELECT QUOTENAME('what''s is your city?', '<') -- Returns <what's is your city?>
SELECT QUOTENAME('what''s is your city?', '>') -- Returns <what's is your city?>

SELECT QUOTENAME('what''s is your city?', '{') -- Returns {what's is your city?}
SELECT QUOTENAME('what''s is your city?', '}') -- Returns {what's is your city?}

SELECT QUOTENAME('what''s is your city?', '`') -- Returns `what's is your city?`

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:

CREATE TABLE [student details]
(
    student_id INT PRIMARY KEY, 
    information  VARCHAR(255)
);
INSERT INTO [student details]
(
    student_id, 
    information
)
VALUES
(
    1, 
    'Enter detailed information here...'
);

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

DECLARE @tablename VARCHAR(130) = 'student details';
DECLARE @sql NVARCHAR(110) = 'SELECT * FROM ' + @tablename;
EXECUTE (@sql);

Output:
There are the following code returns the following error:
Invalid object name 'student'.

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

DECLARE @tablename VARCHAR(130) = 'student details';
DECLARE	@sql NVARCHAR(110) = 'SELECT * FROM ' + QUOTENAME(@tablename);
EXECUTE (@sql);

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.