SQL Server String QUOTENAME() Function

SQL Server QUOTENAME() Function

The SQL Server QUOTENAME() function is used to return a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier. This function is useful for ensuring that identifiers containing special characters or reserved keywords are properly quoted.

Syntax

SELECT QUOTENAME(string, [delimiter]);

The QUOTENAME() function takes two arguments:

  • string: The string to be delimited.
  • delimiter (optional): The delimiter to be used. If omitted, the default delimiter is square brackets ([]).

Example SQL Server QUOTENAME() Function Queries

Let's look at some examples of SQL Server QUOTENAME() function queries:

1. Basic QUOTENAME() Example with Default Delimiter

SELECT QUOTENAME('TableName') AS result;

This query returns the string 'TableName' delimited with square brackets. The result will be:

result
----------
[TableName]

2. QUOTENAME() with a Different Delimiter

SELECT QUOTENAME('TableName', '"') AS result;

This query returns the string 'TableName' delimited with double quotes. The result will be:

result
----------
"TableName"

3. QUOTENAME() with a Column

SELECT column_name, QUOTENAME(column_name) AS quoted_column_name
FROM information_schema.columns;

This query returns the column names from the information_schema.columns table and their delimited versions.

4. QUOTENAME() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'TableName';
SELECT QUOTENAME(@myString, '`') AS result;

This query uses a variable to store a string and then returns the string delimited with backticks. The result will be:

result
----------
`TableName`

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the QUOTENAME() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column_name VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and column_name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, column_name) VALUES (1, 'Name');
INSERT INTO example_table (id, column_name) VALUES (2, 'Age');
INSERT INTO example_table (id, column_name) VALUES (3, 'Address');

Here, we insert data into the example_table.

Step 3: Using the QUOTENAME() Function

This step involves using the QUOTENAME() function to return the delimited column names.

SELECT id, column_name, QUOTENAME(column_name, '"') AS quoted_column_name
FROM example_table;

This query retrieves the id, column_name, and the delimited version of the column_name column for each row in the example_table. The result will be:

id  column_name  quoted_column_name
--- ------------ ------------------
1   Name         "Name"
2   Age          "Age"
3   Address      "Address"

Conclusion

The SQL Server QUOTENAME() function is a powerful tool for returning a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier. Understanding how to use the QUOTENAME() function and its syntax is essential for effective string manipulation and data processing in SQL Server.