SYSK 54: The New Way to Deal with Apostrophes in SQL

  • Article

In SQL, you can now use quotename instead of ‘’’’…  So, if you need to pass O’Brian as the last name, you no longer have to replace it with double apostrophes to make it O’’Brian – just use the new quotename(@lastname, ‘’’’) in your queries.

This function works not just with apostrophes, but on any other delimiters.  In essence, you tell it what delimiter you’re using, and it’ll convert it to the proper SQL string, e.g. right square bracket ] will be replace with two of them to indicate that it’s an escape character…

Important:   this function only works on Unicode strings of 128 characters of less.  Input longer than that will be truncated.

References:  
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/34d47f1e-2ac7-4890-8c9c-5f60f115e076.htm
http://www.windowsitpro.com/Windows/Articles/ArticleID/42216/pg/2/2.html