SQL Functions: QUOTENAME

Hey all, today I am going to show a quick demo on QUOTENAME. When I first discovered this string function, it was one I wished I had learnt long ago as it helps simplify writing out dynamic SQL.

To quote books online, QUOTENAME “Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.” So basically it formats an identifier with delimiters so that in the event that an identifier does not comply with formatting.

The example below returns all the tables in AdventureWorks 2014 with the different types of delimiters that can be added using QUOTENAME.


USE AdventureWorks2014
GO

DECLARE @tables TABLE (
[tableName] SYSNAME
)

INSERT INTO
@tables
SELECT
name AS tablename
FROM
sys.tables

SELECT
tableName AS [Raw],
QUOTENAME(tableName) AS [default],
QUOTENAME(tableName,'''') AS [singleQuoted],
QUOTENAME(tableName,'""') AS [doubleQuoted],
QUOTENAME(tableName,'<>') AS [InequalitySigns],
QUOTENAME(tableName,'@@') AS [Ats]
FROM @tables
GO

If you try this yourself you will notice that the last column returns NULL. This is because the @ symbol is not a valid delimiter, so you cannot pass any character in to wrap it around the field.

The example below takes all the tables and finds their corresponding entry in sys.sysobjects. Using QUOTENAME here means we do not have to specify the quotation marks around the table. There’s nothing wrong with doing that, it’s just that QUOTENAME looks a lot neater. (This is purely for the purposes of demoing the function; clearly you could inner join the two tables together…)


USE AdventureWorks2014
GO
DECLARE @sql NVARCHAR (MAX),
@count INT,
@tablename sysname

DECLARE @tables TABLE (
[tableName] sysname
)

INSERT INTO
@tables
SELECT
name AS tablename
FROM
sys.tables

SELECT
@count = COUNT (*)
FROM
@tables

WHILE @count > 0
BEGIN
SELECT TOP 1
@tablename = tablename
FROM
@tables

SELECT
@sql = 'select
*
from
sys.sysobjects
where
name = '
+ QUOTENAME(@tablename,'''')
from @tables

EXEC (@sql)

DELETE FROM
@tables
WHERE
tableName = @tablename

SET @count = @count - 1
END

All of the tables in AdventureWorks2014 are correctly formatted, so lets add one that isn’t.


use AdventureWorks2014
GO

CREATE TABLE [why would you even add spaces]
(id INT,
name VARCHAR(128)
)

INSERT INTO [why would you even add spaces]
VALUES (1, 'dont ever add spaces in table names')

The below query is going to fail because we do not have a properly formatted table name.


DECLARE @sql NVARCHAR (MAX)

SELECT @SQL = 'SELECT *
FROM ' + name
FROM sys.tables
WHERE
name LIKE '% you even%'
EXEC (@SQL)

GO

Use the QUOTENAME function, and this will run correctly.

DECLARE @sql NVARCHAR (MAX)

SELECT @SQL = 'SELECT *
FROM ' + QUOTENAME (name)
FROM sys.tables
WHERE
name LIKE '% you even%'
EXEC (@SQL)

GO

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s