|
Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
|
|
Apostrophe's and Quotation Marks in SQL ServerBy 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)This will insert a single quote or apostrophe into the field between the "d" and the "A".
|
- Advertisement - |