QUOTENAME Function in SQL Server 2012

This function is used to add square brackets to the starting and ending of a string and how to store strings in various formats in SQL Server. Square brackets are one of the worst things that Microsoft has put into SQL server. In many cases, This function is often used when generating SQL statements dynamically and where the table names, column names or other identifiers can include spaces and brackets. So let's have a look at a practical example of where to use QUOTENAME in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

 

QUOTENAME Syntax

 

QUOTENAME ('char_string' [ ,'quote_char'])

 

char_string is the name of the string and is limited to 128 characters.

 

['quote_char'] Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_char is not specified, brackets are used.

 

Examples

 

The following are some examples of this function with formatted output.

 

If quote_char is not specified with a string.

select quotename('Rohatash Kumar')

Output

 

 

QUOTENAME Function with a left or right bracket ( [] )

select quotename('Rohatash [Kumar]')

Output

 

 

QUOTENAME Function with a double quotation mark ( " )

select quotename('Rohatash Kumar', '"')

Output

 

 

QUOTENAME Function with a single quotation mark ( ' )

select quotename('Rohatash Kumar', '''')

Output

 

 

Creating a table in SQL Server

 

You define a table, named student table which contains a space in the name. So you use square brackets for it.

Create TABLE [student table]
(
            [stu_id] [int] NULL,
            [stu_name] [varchar](20) NULL,
            [marks] [int] NULL,
            [Remarks] [varchar](50) NULL
)

Now Press F5 to execute it.

 

 

Generating SQL statement Dynamically using QUOTENAME Function

 

Now suppose you want to create a dynamic SQL Script which executes a SELECT statement with every Stored Procedure of the database using sys.objects.

 

sys.objects: Contains a row for each user-defined, schema-scoped object that is created within a database.

select 'SELECT * FROM ' + [student table]
from sys.objects
where type_desc='SQL_STORED_PROCEDURE' and create_date <= GETDATE()

Now press F5 to execute it. It will return an error because the table has a space between student and table.

 

 

 

Now using QUOTENAME Function

 

Table has space between student and table. To remove the space of the table between student and table used the QUOTENAME Function, as in:

Select 'SELECT * FROM ' + QUOTENAME('student table')
from sys.objects
where type_desc='SQL_STORED_PROCEDURE' and create_date <= GETDATE()

Now press F5 to execute it. It will show the result.