Escape character problems - MS- SQL Server

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!
LVL 1
vanaudenAsked:
Who is Participating?
 
Ken SelviaRetiredCommented:
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
 
Asta CuTechnical consultant & graphic designCommented:
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
 
vanaudenAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vanaudenAuthor Commented:
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
 
Ken SelviaRetiredCommented:
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
 
vanaudenAuthor Commented:
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
 
Ken SelviaRetiredCommented:
Yes this is expected. To get a quote inside a quoted string you enter 2 quotes.  
0
 
Asta CuTechnical consultant & graphic designCommented:
":0)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.