SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

RocketScientist
Official SQLTeam Chef

USA
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?

rocketscientist

Go to Top of Page

Anonymous
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
strIns

Go to Top of Page

Sameal
Starting Member

USA
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
rob@lorwayco.com
Precision Machining Services. Inc.
Go to Top of Page

wevans
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:

Replace(Request.Form("DaInBox"),"'",Chr(146))

Go to Top of Page

sqlMichael
Starting Member

Canada
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.
EXMAMPLE:
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
Select
'Client Name' = QUOTENAME(CLNAME,'"')
From TABLE

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.

sglmichael
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000