Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Apostrophe's and Quotation Marks in SQL Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 08/07/2000 :  14:23:53  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Article Link.

Official SQLTeam Chef

85 Posts

Posted - 08/10/2000 :  14:54:35  Show Profile  Visit RocketScientist's Homepage  Reply with Quote
OK, that's one way

The other way to do it is to use the new "Quotename" function in SQL Server:

print quotename(@lastName, '''')

That's a little bit confusing, but what it returns is the value in @LastName with all of the apostrophe's escaped out with a double-apostrophe. You could also do this:

print quotename(@LastName, "'")

Quotename also does other interesting things, like this:

print quotename('This is a test', '{}')

will print
{This is a test}

Pretty nifty, eh?


Go to Top of Page

Starting Member

0 Posts

Posted - 04/04/2001 :  09:23:38  Show Profile  Reply with Quote
Apostrophe Problem

PLEASE HELP! I can't figure out exactly how to use your VB apostrophe fix. I keep getting this error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Jill O'Reilly','VISN3's Webmaster''.
/BegASPdatabases/chapter.10/nceconsult2.asp, line 314 (line 314 is the objCmd.Execute command!!!)

What am I doing wrong in my HTML page below):

'Declare variables needed
Dim strInsert
Dim strValues
Dim adCmdText

'Set required variables
adCmdText = 1

'* If an Add was requested, add the new consult to the database

'change apostrophes to quote marks in text fields
Requestor_Name = replace (Requestor_Name,"'","''")
Requestor_Title = replace (Requestor_Title,"'","''")

If Request.Form("Action") = "Add" Then

'Start building the SQL strings with the required fields
strInsert = "Insert into NCEConsult (Requestor_Name,Requestor_Tel"
strValues = "Values('" & CStr(Request.Form("txtRequestorName")) & _
"','" & CStr(Request.Form("txtRequestorTel")) & "'"

'Add contact method if present
If Len(Request.Form("txtContactMethod")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",Contact_Method"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtContactMethod")) & "'"
End If

'Add requestor title if present
If Len(Request.Form("txtRequestorTitle")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",Requestor_Title"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtRequestorTitle")) & "'"
End If

'Add requestor address if present
If Len(Request.Form("txtRequestorAddress")) > 0 Then
'Add the column name to the insert string

Go to Top of Page

Starting Member

1 Posts

Posted - 08/05/2001 :  18:49:56  Show Profile  Reply with Quote
After spending six hours today pulling my hair out over this little apostrophe problem, you all fix me up in 1 minute or less. Thanks.

Robert Padgett
Precision Machining Services. Inc.
Go to Top of Page

Starting Member

1 Posts

Posted - 11/25/2002 :  01:26:00  Show Profile  Reply with Quote
I actually think the best way on both inserts and subsequent queries against the data is to use Chr(146).

Something like this:


Go to Top of Page

Starting Member

1 Posts

Posted - 10/23/2009 :  17:44:32  Show Profile  Reply with Quote
I've creaetd a sProc that uses the QUOTENAME function, it runs fine in SQL Server 2005, but when I add the sProc to a reporting service, the output file now has triple the amount of quotes I was expecting.
Query returns "NAME"
Using a subscription service, the output is now """NAME"""
I've setup the subscription to output with as a comma delimited CSV file.

Query is setup as
'Client Name' = QUOTENAME(CLNAME,'"')

Is the triple quotes a result of the use of QUOTENAME, or a result of the reporting service?
Sorry if this topic should be posted under a differnt area. It's my first post.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000