QUOTENAME function in SQL Server

In this SQL Server tutorial, you will learn how to put the character around the string using the QUOTENAME function in SQL Server.

You will understand how the QUOTENAME() function works and why you should use it. Then, with an example, you will understand how it wraps the string with characters.

You will use the QUOTENAME() function with a different delimiter and also with a variable.

Table of Contents

QUOTENAME function in SQL Server

QUOTENAME function in SQL Server adds the delimiters around the string and returns the string with delimiter around it. This returned string is called a Unicode string. Adding the delimiter around the string is to interpret it as a valid SQL Server delimited Identifier.

The QUOTENAME() function is usually used in dynamic SQL to correctly deal with identifiers like column names or tables. Also, when the delimiter is added around the string to make it a literal value or Identifier, it prevents SQL injection attacks.

The syntax is given below.

QUOTENAME ( 'character_string'  , 'quote_character')

Where,

QUOTENAME(): This is the function which quotes the string.
character_string: It is the characters or strings around which you want to add the delimiters (quote). You can use the 128-character long string; passing more than 128 characters returns the null value.
quote_character: It is a single character which is used as a delimiter, and this character is added around the string as a delimiter. It can be the following characters, such as a single quotation mark ( ‘ ), a double quotation mark ( ” ), a left or right parenthesis ( () ), a left or right bracket ( [] ), a greater than or less than sign ( >< ), a backtick ( ` ) or a left or right brace ( {} ).

The return type of the QUOTENAME() function is nvarchar(258).

Let’s understand with an example, suppose you have a string containing a country name ‘United Kindom, USA, Canada’, and you want to put brackets around it.

So, for that, use the query below.

SELECT QUOTENAME('United Kindom, USA, Canada') AS QuotedString;

As you can see in the output, the result set contains a string [United Kindom, USA, Canada], and the quote character bracket [] is around the string; also, after adding the delimiter, the string becomes a valid SQL Server delimited Identifier.

Look in the query part. The quote character [] is not specified but is still added. That’s because of the default behaviour of the QUOTENAME() function when you don’t specify any quote character.

This is how to add the delimiter or quote character around the string using the QUOTENAME function in SQL Server.

QUOTENAME function in SQL Server with Different Delimiter

In the above section, you haven’t specified the quote character or delimiter in the QUOTENAME() function in SQL Server. But you can also specify the custom delimiter or quote character that you learned while going through the syntax of this function.

Let’s take the same example as the previous one, and instead of a bracket, this time, add the parenthesis () around the string ‘United Kindom, USA, Canada’, so use the query below.

SELECT QUOTENAME('United Kindom, USA, Canada','()') AS QuotedString;

QUOTENAME function in SQL Server with Different Delimiter

As you can see from the output of the above query, the result set contains the string (United Kindom, USA, Canada), which has parenthesis around it.

So here in the query part QUOTENAME(‘United Kindom, USA, Canada’,'()’), the second argument represents the delimiter or quote character you want to put around the string.

Instead of parenthesis, you can specify a different delimiter or quote character such as a single quotation mark ( ‘ ), a double quotation mark ( ” ), a greater than or less than sign ( >< ), a backtick ( ` ) or a left or right brace ( {} ).

This is how to use the QUOTENAME function in SQL Server to quote strings with different delimiters.

QUOTENAME function in SQL Server with Variable

Suppose you have a variable containing a string value and want to add the delimiter around that string in a variable. In that case, you can pass the variable to the QUOTENAME() function.

For example, you have @string store string value ‘2, 3, 4, 5’ and want to add the curly braces around it; for that, use the query below.

DECLARE @string_data VARCHAR(10) = '2,3,4,5';

SELECT QUOTENAME(@string_data,'{}') AS QuotedString;

QUOTENAME function in SQL Server with Variable

From the output, the curly braces {} around the string are added, as you can see in the result set {2,3,4,5}. You have passed the @string_data variable to the QUOTENAME() function with quote character {}.

This is how to use the QUOTENAME function in SQL Server with a variable to add the quote character around the string in a variable.

Conclusion

In this SQL Server tutorial, you learned how to add the delimiter or quote character, such as parenthesis, brackets, etc, around the string using the QUOTENAME function in SQL Server.

Also, you used a different delimiter with the QUOTENAME() function. Finally, you learned how to quote the string value in a variable by passing the variable to the QUOTENAME() function with a delimiter.