[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Escape character problems - MS- SQL Server

Posted on 2004-10-25
8
Medium Priority
?
38,921 Views
Last Modified: 2011-08-18
Hi

How do you escape an american apostrophe (') to be input into a text field in MS SQL Server 2000

I don't have a choice but to use an actual apostrophe because this is for a language course.

Please help me ASAP

I know 500 points is alot for this...but I'm in a super rush (AKA --> I want to go home)!!!

Thanks!
0
Comment
Question by:vanauden
  • 3
  • 3
  • 2
8 Comments
 
LVL 27

Expert Comment

by:Asta Cu
ID: 12406981
Does this help?  Not my area of expertise, but it sounds 'urgent', so giving it a try.

Also: there are certain characters that if not given a backslash can cause problems with SQL commands. A good example of this is the apostrophe ('). If you try to create an item whose Name property is "Stuart's Brin-Gun", the apostrophe will be seen by the SQL command and create an error on the server end (returning an exception on the client end). Thus, you should call the item "Stuart\'s Brin-Gun". Since most user-input doesn't have escape characters, you will have to manipulate the string to modify the escape character before you execute the command. Use either a regular expression or StringFilter.ReplaceIgnoreCase(), or find your own way of doing it.
http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=454
0
 
LVL 12

Accepted Solution

by:
Ken Selvia earned 2000 total points
ID: 12407000
Use two quotes inside quotes to get a quote.

update mytable set col = '''This is quoted'''

Or use char(39)

update mytable set col = char(39) + 'This is quoted' + char(39)
0
 
LVL 1

Author Comment

by:vanauden
ID: 12407077
kselvia's answer is the one.

In SQL server 2000 the backslash does not act as an escape character. Tried it.

Thank you both for the quick response
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:vanauden
ID: 12407134
Actually, I just realized that kselvia was almost right. this is the correct answer: (but I won't retrieve the points)

the first single quote acts as the escape character and the second single quote is the one that is written to the db table.

so it should be something like this:

update mytable set col = ''This is quoted''
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12407154
I don't know why you are getting different results but my original suggesiton should be right.

create table mytable (col1 varchar(30))

insert mytable select '''Quoted'''
select * from mytable

col1                          
------------------------------
'Quoted'

insert mytable select ''Quoted''

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

0
 
LVL 1

Author Comment

by:vanauden
ID: 12407298
I think we are both right kselvia, but in different contexts:

you need two single quotes around a single quote if they are beginning or ending the string; however, if a quote is in the middle of the string, you need only one quote to escape it, or you get an error.

try this (gives error):

create table mytable (col1 varchar(30))

insert mytable select '''Olivia d'''Abo'''
select * from mytable

Error is --

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''
select * from mytable

and now try this (this works):

insert mytable select '''Olivia d''Abo'''
select * from mytable

I find this strange, but if you happen to know why, I would be interested



0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12407310
Yes this is expected. To get a quote inside a quoted string you enter 2 quotes.  
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 12407472
":0)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question