Apostrophe's and Quotation Marks in SQL Server

By Bill Graziano on 13 August 2000 | 5 Comments | 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".

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

some help pls.... (2 Replies)

Calculated Field (16 Replies)

Compare Rows in same Table for tracking changes. (0 Replies)

filtering out duplicate rows based on three column (6 Replies)

Numbers to show as pound £ (1 Reply)

Check a value against multiple columns in diff tab (4 Replies)

CTE to temp table (2 Replies)

patient cycle time (0 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -