Post reply

Embedding Single Quote in text string in INSERT INTO value

  • November 25, 2005 at 2:41 am

    #364324

    In SQL7 I want to include a single quote embedded in a text string to be sent as a field data value:

    Cote D'Ivoire

    When this string gets delimited by single quotes in my INSERT INTO statement, I get a syntax error, as does using doublequote delimiters, or just replacing the embedded single quote by two consecutive ones.

    Can someone please advise precise syntax to solve this?

    Many thanks

    Robin Pearce  

  • November 25, 2005 at 4:54 am

    #605833

    Not sure about SQL7, but in SQL Server 2000, you could consider using

    SET QUOTED_IDENTIFIER

    to get round this.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • November 25, 2005 at 5:38 am

    #605840

    REPLACE(@str,'''','''''')

    Works in SQL 2000, can't say about 7.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 25, 2005 at 7:42 am

    #605846

    SET QUOTED_IDENTIFIER solved my problem.

    thanks

    Robin P

  • December 9, 2005 at 7:47 am

    #608659

    Using two consecutive single-quotes should always work, where using double-quote to identify a string will be sensitive to the QUOTED_IDENTIFER setting, which I suspect you set to OFF.  I'd recommened in new development you use consecutive single-quotes.

     

    This should always work.

    insert (colname)  select 'Someone''s Name' --two single quotes after "someone"

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply