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..
2,663 total views, 2 views today