Question: How to Remove Quotes of QUOTENAME?
Answer: This was a very interesting question, I received based on my yesterday’s blog post SQL SERVER – QUOTENAME Function and Custom Quote Character. One of the users followed the direction of the blog post and updated the database columns without thinking about the impact of the column. He ended up every single value of his database with the additional quotes around it.
For example, the columns in the database looked like as follows:
Now his question was that how can he undo the update which he had done. One method was to use the function LEFT or RIGHT and remove the first and last character and update the column back. However, before he executes that he wanted to know if there is any way he can undo his changes.
There is an interesting function – PARSENAME, you can use that to reverse the impact of the QUOTENAME function if you have used it to apply [ or ] to your string.
I asked him to update his table with the column PARSENAME(StringName, 1) and he was able to get his original values back as displayed in the image.
Particularly PARSENAME function can be used for many other usages. I will write a separate blog post to demonstrate how passing different parameters in the second value we can get different values and how we can use it creatively in our application.
Here are a few previous blog posts related to interview questions and answers.
- Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234
- How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233
- What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232
- What is Faster, SUM or COUNT? – Interview Question of the Week #231
- What are the Different Types of SQL Server CHECKPOINT? – Interview Question of the Week #230
- How Dirty or Clean is SQL SERVER’s Memory? – Interview Question of the Week #229
Reference: Pinal Dave (https://blog.sqlauthority.com)
