Quoth the Raven… Nevermore

Sometimes you’ll find a fun little function that can actually help you out a lot with your coding. Today’s function is just that for me.

When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”

Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!

For example, let’s get all of the columns from one of our favorite tables!

SELECT C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = 'NirvanaPerformances'

Now to put the brackets around the column names – like a good developer – we just change it to:

SELECT QUOTENAME(C.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = 'NirvanaPerformances'

Now I don’t know about y’all but I’ve seen more than a few tables that have column names with spaces or unprintable characters in them. The only way I can do diddly with a column name if there is a space or an unprintable character in it is if it is surrounded by brackets.

For example, let’s say there is a table like the following:

CREATE TABLE dbo.BadStyling
( ID INT IDENTITY PRIMARY KEY,
  [First Name] VARCHAR(50) NOT NULL,
  [Last Name ] VARCHAR(50) NOT NULL,
  [Full Name] VARCHAR(100) NOT NULL,
  [Annual Salary] MONEY NOT NULL,
  [Performance / Week] DECIMAL NOT NULL)

Next, we want to insert some data in this table so that we can see what we’re doing with it.

INSERT INTO dbo.BadStyling ([First Name], [Last Name ], [Full Name], [Annual Salary], [Performance / Week])
SELECT 'Fred', 'Flintstone', 'Fred Flintstone', 70000, 1.2 UNION ALL
SELECT 'Barney', 'Rubble', 'Barney Rubble', 40000, 2.3

The first question you should ask yourself – can I get away without typing the brackets around the first name column? Sadly, no.

Now, the good news though is with that Last Name column. If there is a space at the end of the column name – then SQL Server is smart enough to let you leave that out.

And that fun Performance / Week column – yes, that has to have the brackets around it.

But, now, if I want to put all of the columns into one string, then I can do the following – using some of the items I learned in last week’s post!

SELECT STUFF((
	SELECT ', ' + QUOTENAME(C.COLUMN_NAME) 
	FROM INFORMATION_SCHEMA.COLUMNS C
	WHERE C.TABLE_NAME = 'NirvanaPerformances'
	FOR XML PATH('')
	), 1, 2, '');

When I need to figure out all of the columns in a table on the fly, this is how I do it, my friends!

Now, let’s say that I don’t really want to have my data bracketed by brackets. Let’s say that we want it bracketed by double-quotes. I can still use QUOTENAME, but with the optional parameter.

SELECT QUOTENAME(‘Goodness’, ‘”‘)

Yes, that is a single quote, a double quote, and another single quote.

And with that my friends, we come to the end of another post. Hopefully, you’ll find a few good uses for the QUOTENAME function in your upcoming projects! Until next time my friends!