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.

SQL SERVER - QUOTENAME Function and Custom Quote Character customquote-800x333

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.

SQL SERVER - QUOTENAME Function and Custom Quote Character quote1

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 }.

SQL SERVER - QUOTENAME Function and Custom Quote Character quote2

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.

SQL SERVER - QUOTENAME Function and Custom Quote Character quote3

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

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

, , ,
Previous Post
SQL SERVER – Number of Rows Read Per Threads in Parallel Operations
Next Post
SQL SERVER – List All Frequently Ran Stored Procedure From Server Cache

Related Posts

1 Comment. Leave new

  • Shantilal Suthar
    July 29, 2019 11:42 am

    Hi Pinal,

    Can you give any real life example of this situation?

    Thanks.

    Reply

Leave a Reply

Menu