SQL Server QUOTENAME Function

Summary: in this tutorial, you will learn how to use the SQL Server QUOTENAME() function to make a string a valid SQL Server delimited identifier.

SQL Server QUOTENAME() function

The SQL Server QUOTENAME() function adds delimiters to an input string to make that string a valid SQL Server delimited identifier.

The following shows the syntax of the QUOTENAME() function:

QUOTENAME ( input_string [ , quote_character ] )   
Code language: SQL (Structured Query Language) (sql)

The QUOTENAME() function accepts two arguments:

• input_string is a SYSNAME whose maximum length is 128. If the length of the input_string is greater than 128 characters, the function will return NULL.
• quote_character is a character that uses as the delimiter.

The following are valid quote characters:

• A single quotation mark ( ‘ )
• A left or right bracket ( [] )
• A double quotation mark ( ” )
• A left or right parenthesis ( () )
• A greater than or less than sign ( >< )
• A left or right brace ( {} )
• A backtick ( ` ).

If you use an invalid character, the function will return NULL. The quote_character defaults to brackets if you skip it.

The SQL Server QUOTENAME() is useful in dynamic SQL.

SQL Server QUOTENAME() function example

The following statements create a new table whose name contains a space and insert a new row into the table:

CREATE TABLE [customer details]
(
    customer_id INT PRIMARY KEY, 
    info        VARCHAR(255)
);

INSERT INTO [customer details]
(
    customer_id, 
    info
)
VALUES
(
    1, 
    'detailed information...'
);
Code language: SQL (Structured Query Language) (sql)

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

DECLARE @tablename VARCHAR(128) = 'customer details';
DECLARE @sql NVARCHAR(100) = 'SELECT * FROM ' + @tablename;
EXECUTE (@sql);
Code language: SQL (Structured Query Language) (sql)

It returns the following error:

Invalid object name 'customer'.    Code language: JavaScript (javascript)

The following code makes the table name valid by using the QUOTENAME() function:

DECLARE @tablename VARCHAR(128) = 'customer details';
DECLARE	@sql NVARCHAR(100) = 'SELECT * FROM ' + QUOTENAME(@tablename);
EXECUTE (@sql);  
Code language: SQL (Structured Query Language) (sql)

Here is the output:

customer_id info
----------- -----------------------
1           detailed information...

(1 row affected)

In this tutorial, you have learned how to use the SQL Server QUOTENAME() function to add delimiters to a string and makes that string a valid SQL Server delimited identifier.