I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the QUOTENAME() function today.
The QUOTENAME() function takes a input of type sysname, nvarchar(128) and returns a string, nvarchar(258) with quote characters at front and end of the string. A optional string can be specified to redefine the default quote character for TSQL.
The following example creates a table name in the adventure works database from two variables.
|
1 2 3 4 5 6 |
-- Set local variables declare @var_schema sysname = 'SalesLT'; declare @var_name sysname = 'Product'; -- Make table name from ADW select quotename(@var_schema) + '.' + quotename(@var_name) as my_name; |
|
1 2 3 4 5 |
output: my_name ------------------- [SalesLT].[Product] |
The default quote characters [] can be changed by supplying a valid value. It is undocumented what is valid in Books Online. I did notice that {}, (), and <> work via some testing on my own.
However, the exclamation point is not considered a valid quote character. A invalid quote character converts the output string to NULL.
|
1 2 3 4 5 |
-- Change quote character (valid character) select quotename('AdventureWorks', '"') as my_name; -- Change quote character (invalid character) select quotename('AdventureWorks', '!') as my_name; |
|
1 2 3 4 5 6 7 8 9 |
output: my_name --------------- "AdventureWorks" my_name --------------- NULL |
One thing to always worry about when using TSQL functions is how will it react with a EMPTY strings or NULL values?
|
1 2 3 4 5 |
-- Empty string returns brackets select quotename('') as my_name; -- Null returns Null select quotename(NULL) as my_name; |
|
1 2 3 4 5 6 7 8 9 |
output: my_name --------------- [] my_name --------------- NULL |
Next time, I will be exploring the REPLACE() function.
