[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Escape character problems - MS- SQL Server

Posted on 2004-10-25
8
Medium Priority
?
38,910 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
kselvia 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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