SQL QUOTENAME Function Use and Examples

Overview

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

Explanation

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:

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

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.

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

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 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

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 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

Additional Information

• SQL LTRIM
• SQL RTRIM