Apostrophe's and Quotation Marks in SQL Server

By Bill Graziano on 13 August 2000 | Tags: Data Types

---

DJM writes "How do I deal with a variable in a query that may or may not contain an apostrophe, such as "d'Arby"? My SQL runs fine until it hits such an entry, and then of course stops cold, thinking the value is "d", leaving some unknown bogus command called "Arby'". I've given myself migraines over this. I would appreciate it no end if you could help me out of this dilemna. I'm using ASP on IIS with Transact SQL." This Ask SQLTeam has been updated with some additional information on apostraphe's since this seems to be a very popular question.

I actually had the exact same problem when I was working on the code for SQLTeam. As you'll notice, the ' in your question made it into the database just fine so I did something right. What you need to do is convert each single apostraphe into a pair of apostraphes. I did this using the replace function in VBScript. It looked something like this:

fStory = replace ( fStory ,"'","''")

SQL Server will interpret a pair of apostraphes as a single apostraphe in the database. The replace statement works just fine even if there are no apostraphes in the string. This should work equally well for Visual Basic or any other development tool.

The SQL syntax to insert a string like this looks something like:

INSERT Table1 (chColumnName)
VALUES ('Terrence Trent d''Arby')

This will insert a single quote or apostrophe into the field between the "d" and the "A".

Related Articles Using the TIME data type in SQL Server 2008 (6 March 2008) Using the DATE data type in SQL Server 2008 (6 December 2007) Working with Time Spans and Durations in SQL Server (15 October 2007) DATEDIFF Function Demystified (20 March 2007) The Cost of GUIDs as Primary Keys (8 January 2005) Search and Replace in a TEXT column (18 January 2004) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables (7 December 2003) How to search for date and time values (2 May 2003)

Other Recent Forum Posts How to set a variable from a table with comma? (4h) SSRS Expression IIF Zero then ... Got #Error (1d) Understanding 2 Left Joins in same query (1d) Use a C# SQLReader to input an SQL hierarchyid (2d) Translate into easier query/more understandable (2d) Aggregation view with Min and Max (2d) Data file is Compressed - Cannot Re-Attach, Cannot Restore (2d) Sql trigger assign value to parameter (6d)