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.

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:

And you get the following error:

Now try the same code with the QUOTENAME function:

And this works :)


Interesting function; comments and usage welcome !