SQL QUOTENAME() String Function

The SQL QUOTENAME() function adds delimiters around a given string, typically used for enclosing identifiers like table or column names. By default, it uses square brackets [] in SQL Server, but you can specify other delimiters, such as double quotes "".

This function is particularly helpful for dynamically building SQL queries and ensuring identifiers with special characters or spaces are correctly handled. The QUOTENAME() function is available in SQL Server.

In this tutorial, we will go through SQL QUOTENAME() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL QUOTENAME() Function

The basic syntax of the SQL QUOTENAME() function is:

</>
Copy
QUOTENAME(string, delimiter);

Each part of this syntax has a specific purpose:

  • string: The text or identifier you want to enclose in delimiters.
  • delimiter (optional): The character to use for delimiting the string, such as '[', ']', '", or '`'. If not specified, QUOTENAME() defaults to square brackets [].

The QUOTENAME() function returns the input string enclosed within the specified delimiters.

Examples: Using QUOTENAME() in SQL Queries

We’ll go through examples demonstrating the QUOTENAME() function in SQL, focusing on enclosing identifiers and using different delimiters.

1. Enclosing an Identifier with Default Delimiters

To enclose a table name Customer Orders with default square brackets:

</>
Copy
SELECT QUOTENAME('Customer Orders') AS quoted_identifier;

This query returns [Customer Orders], safely enclosing the identifier in brackets, useful if the identifier contains spaces.

2. Using QUOTENAME() with Double Quotes as Delimiters

To enclose the identifier Product.Name in double quotes:

</>
Copy
SELECT QUOTENAME('Product.Name', '"') AS quoted_identifier;

This query returns "Product.Name", making it SQL-compliant for systems that support double-quoted identifiers.

3. Enclosing an Identifier with Backticks

To enclose the column name Order ID in backticks for compatibility with MySQL-style syntax:

</>
Copy
SELECT QUOTENAME('Order ID', '`') AS quoted_identifier;

This query returns `Order ID`, which is useful for MySQL-style syntax where backticks are used for identifier quoting.

4. Using QUOTENAME() for Dynamic SQL

To dynamically build a SQL statement with a table name that might contain special characters:

</>
Copy
DECLARE @tableName NVARCHAR(50) = 'Order Details';
DECLARE @sql NVARCHAR(1000) = 'SELECT * FROM ' + QUOTENAME(@tableName);
EXEC sp_executesql @sql;

This query dynamically builds and executes a SQL statement, enclosing the table name safely to prevent syntax errors due to spaces or special characters.

FAQs for SQL QUOTENAME()

1. What does the SQL QUOTENAME() function do?

The QUOTENAME() function encloses a string in specified delimiters, usually for safely quoting identifiers like table or column names.

2. What is the default delimiter for QUOTENAME()?

The default delimiter for QUOTENAME() is square brackets [] in SQL Server.

3. Can QUOTENAME() be used with special characters?

Yes, QUOTENAME() can be used to enclose identifiers with spaces or special characters, ensuring compatibility with SQL syntax.

4. Is QUOTENAME() supported in all SQL databases?

No, QUOTENAME() is specific to SQL Server. Other databases may use different methods for quoting identifiers.

5. What delimiters can I use with QUOTENAME()?

With QUOTENAME(), you can use [], "", `, or other valid delimiters. If none is specified, it defaults to [].