Solved

Escape character problems - MS- SQL Server

Posted on 2004-10-25
38,830 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
Question by:vanauden
    8 Comments
     
    LVL 27

    Expert Comment

    by:Asta Cu
    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:
    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
    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
     
    LVL 1

    Author Comment

    by:vanauden
    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
    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
    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
    Yes this is expected. To get a quote inside a quoted string you enter 2 quotes.  
    0
     
    LVL 27

    Expert Comment

    by:Asta Cu
    ":0)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    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…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    931 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now