Home » SQL QUOTENAME Function

SQL QUOTENAME Function

SQL QUOTENAME function is a string function which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.





SYNTAX

QUOTENAME ( character_string [ , quote_character ] )

character_string
Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

quote_character
is a character that uses as the delimiter. It can can be a left or right bracket ( [] ), a left or right parenthesis ( () ), a left or right brace ( {} ), a single quotation mark ( ), a double quotation mark ( ), a backtick ( ` ), or a greater than or less than sign ( >< ).

It returns null when invalid character is specified. If omitted, brackets are used as the delimiter.

Lets look at an example of using QUOTENAME function in SQL.

Usage of QUOTENAME function

Example 1:

By default QUOTENAME function adds square brackets if delimiter is not specified.

As you can in following query QUOTENAME function adds a square brackets in string ‘SQL Server’.

Example 2:

Lets add a double quotation mark ( ) to the string SLQ  Server  to make it a valid SQL Server delimited identifier using QUTOENAME function.

SELECT QUOTENAME('SQL Server', '"') AS Result

You can see, QUOTENAME function adds the double quotation mark to the string.

Example 3:

Lets assume, you have table named Tran Data which contains a space in the name. So you used a square brackets for it.

Following is the scripts which create new a table whose name contains a space and insert a new row into the table.

CREATE TABLE dbo.[Tran Data]
(
Tran_Id INT PRIMARY KEY IDENTITY(1,1), 
Tran_Code VARCHAR(50)
);

INSERT INTO dbo.[Tran Data]
(
Tran_Code
)
VALUES
(
'XYZ045'
);


SELECT * from dbo.[Tran Data]

Now you use dynamic SQL query which fetches the data from table [Tran Date]

DECLARE @table_name VARCHAR(50) = 'Tran Data';
DECLARE @sqlquery NVARCHAR(150) = 'SELECT * FROM ' + @table_name;
EXECUTE (@sqlquery);

As you can see, it returns an error as there is space between table name and you have not specified a square brackets with table name in dynamic SQL query.

Lets make the table name valid using QUOTENAME function.

DECLARE @table_name VARCHAR(50) = 'Tran Data';
DECLARE @sqlquery NVARCHAR(150) = 'SELECT * FROM ' +QUOTENAME(@table_name);
EXECUTE (@sqlquery);

As you can see, it does not give any error and returns the data from table [Tran Data].

QUOTENAME function adds a square brackets with table name which makes a table name valid.

You can also see, how the QUOTENAME function added a square brackets with table name by using a PRINT statement to return dynamic SQL Statement.

DECLARE @table_name VARCHAR(50) = 'Tran Data';
DECLARE @sqlquery NVARCHAR(150) = 'SELECT * FROM ' +QUOTENAME(@table_name);
PRINT (@sqlquery);

Specifying Invalid character string to QUOTENAME function

The character string is limited to 128 characters so providing any character string that is greater than 128 characters, QUOTENAME function return NULL.

Lets specified a long character string to the QUOTENAME function.

SELECT QUOTENAME('SQL QUOTENAME function is a string function which returns a 
Unicode string with the delimiters added to make the input string a valid SQL 
Server delimited identifier.')

As you can see, it returns null for too long character string.




Also Read..

SQL Server String Functions

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.