SQL SERVER – QUOTENAME Function and Custom Quote Character

Today, I have received a very interesting question from my client with whom I have worked in the past on Comprehensive Database Performance Health Check. The question was really not related to SQL Server Performance Tuning but DBA wanted to know if they can use any custom Quote Character for their string. Let us understand the question a bit more in detail.

If you are using any special character your string often during the display time or while running dynamic SQL the string may break.

For example, if you have a string as such My[]Name with brackets in it, when you try to display it or assign to another variable, it may give us error as it contains a single quote in it. In that case, we can use the function QuoteName. Let us see how it works.

Function QUOTENAME

SELECT QUOTENAME('My[]Name')

The above script will give us the following result where we have brackets around the string name.

However, the question from the user was how to change the brackets which are around the string. Well, that is also very simple.

Custom Quote Character

If you want to change the custom quote character, you can easily do that by passing a different value as a second parameter in the QUOTENAME function.

SELECT QUOTENAME('My[]Name','{}')

When you run the above script our string is now wrapped with { and }.

Please note that the special characters are allowed are any of the following:

• single quotation mark ( ‘ )
• left or right bracket ( [] )
• double quotation mark ( “ )
• left or right parenthesis ( () )
• greater than or less than sign ( >< )
• left or right brace ( {} ) or a backtick ( ` )

If you pass any other value, the above script will give you a NULL value. For example, if you pass $ as the following script, it will give you NULL value.

This can be a very useful feature if you are using any special characters in your string.

Reference: Pinal Dave (https://blog.sqlauthority.com)