SQL Server QUOTENAME Function


By:

The QUOTENAME function is used to provide string with delimiters. By default, the delimiter is left and right brackets.

Syntax

QUOTENAME(expression, [delimiter])

Parameters

  • expression - this is the character string or expression to be delimited.
  • delimiter - optional parameter to define the delimiter. The default is square brackets if not specified.

The following delimiters can be used:

Delimiter Description
' single quote 
" double quote
[] left or right bracket
() left or right parenthesis
<> less than or greater than
{} left or right brace
` backtick

Simple QUOTENAME Example

The following example will delimit the string with square brackets which is the delimiter by default.

SELECT QUOTENAME('My value') as output
T-SQL quotename default delimiter

QUOTENAME Options Example

Below shows the output for all of the different delimiters. The query just unions all of the results together so the output is in one list.  The sortOrder is used so the query results are in the same order as the options in the query.

SELECT 'no delimiter' as delimiter, QUOTENAME('My value')       as output, 1 as sortOrder
UNION
SELECT ''' delimiter' as delimiter, QUOTENAME('My value', '''') as output, 2 as sortOrder
UNION
SELECT '" delimiter'  as delimiter, QUOTENAME('My value', '"')  as output, 3 as sortOrder
UNION
SELECT '[] delimiter' as delimiter, QUOTENAME('My value', '[]') as output, 4 as sortOrder
UNION
SELECT '() delimiter' as delimiter, QUOTENAME('My value', '()') as output, 5 as sortOrder
UNION
SELECT '<> delimiter' as delimiter, QUOTENAME('My value', '<>') as output, 6 as sortOrder
UNION
SELECT '{} delimiter' as delimiter, QUOTENAME('My value', '{}') as output, 7 as sortOrder
UNION
SELECT '` delimiter'  as delimiter, QUOTENAME('My value', '`')  as output, 8 as sortOrder
ORDER BY sortOrder

Here is the output.

all quotename options

Example of QUOTENAME with Parenthesis in the String

In this example, we are using parenthesis and the output shows double close parenthesis to denote an escape character.

SELECT QUOTENAME('My (value)', ')') as output
Quotename sql server example with escape

NULL Output for the QUOTENAME function

If the delimiter is not valid, a NULL value will be returned. In this example we use a hyphen as a delimiter which is an invalid delimiter, so we get NULL.

SELECT QUOTENAME('My value', '-') as output
quotename returning NULL values

QUOTENAME Example Using Closed Delimiter

You can use just the open or closed delimiter. In this example we are using the less than sign, but we could also use the greater than sign with the same output. This works the same for all of the options that have an open and closed delimiter.

SELECT QUOTENAME('My value','>') as output
Quotename with >

Using QUOTENAME with Columns

The following example shows how to quote using parenthesis in the table Person.EmailAddress.

SELECT QUOTENAME(EmailAddress, '()') as email FROM Person.EmailAddress
-- or we could just do this
SELECT QUOTENAME(EmailAddress, '(') as email FROM Person.EmailAddress
quotename function using tables

QUOTENAME Ignores Additional Delimiter Characters

If we use the following, the function still works using the first character and ignores any of the additional characters for the delimiter.

SELECT QUOTENAME('My value','<abc') as output
Quotename with >

Related Articles


Last Update: 11/9/2021




Comments For This Article





download














vote


get free sql tips
agree to terms