Embedding Single Quote in text string in INSERT INTO value

• Robin Pearce

  SSC Veteran

  Points: 205

  More actions

  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  

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  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.

  ---

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• Robin Pearce

  SSC Veteran

  Points: 205

  More actions

  November 25, 2005 at 7:42 am

  #605846

  SET QUOTED_IDENTIFIER solved my problem.

  thanks

  Robin P

• BReuter

  Ten Centuries

  Points: 1325

  More actions

  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"