SQL Server QUOTENAME function

Hi Friends,

Here is an interesting function, SQL Server QUOTENAME function: QUOTENAME() which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. (from Books Online)

For example:


The default delimiter is square brackets, unless you specify any of the other two delimiters (single quote β€˜ or double quote β€œ)

So where and how can this function be used?

Dynamic SQL is a good example. Suppose you have object names that contain spaces and you want to extract these object names from system catalogs to be used in your dynamic code – you will have to enclose them in delimiters.

For example;

Let us create a table in AdventureWorks that will contain spaces in the object name. After creating a sample table, we will insert a record into it.

use AdventureWorks
create table [a funny table with spaces]
(id int)
insert into [a funny table with spaces]
values (1)

Now, if you query the sys.objects, you will get the object name (observe that it is without the delimiters)



Now; you want extract this object name and want to use in your dynamic code but it won’t work if you do not use the QUOTENAME function. Let us first try without the QUOTENAME function:

declare @tablename nvarchar(200) ='';

select @tablename = 'select * from ' + name + ';'
FROM sys.objects where name like '%funny%'
EXECUTE (@tablename);

And you get the following error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Now try the same code with the QUOTENAME function:

declare @tablename nvarchar(200) ='';
select @tablename = 'select * from ' + QUOTENAME(name) + ';'
FROM sys.objects where name like '%funny%'
EXECUTE (@tablename);

And this works πŸ™‚


Interesting function; comments and usage welcome !




About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

7 Comments on “SQL Server QUOTENAME function”

  1. Neat function, but why couldn’t you just do this?:

    select @tablename = ‘select * from [‘ + name + ‘];’

    Do you know of a scenario where the quotname funtion should be used exclusively?

  2. Hi Jaosn, sorry for the late reply.. – no i cant think of any specific right now.. just that the function seems to be easy to use,,

Leave a Reply

Your email address will not be published.