How to ignore Apostrophes ' and comments -- while updating

• Viswanath S. Dhara

  SSC Veteran

  Points: 234

  More actions

  May 29, 2008 at 1:08 am

  #191079

  Hi,

  I have a varchar(8000) field. I have to update it periodically appending it. But the string contains reserved characters.

  For example The' Big -- Blue Fox 'Jumped Over the fen'ce but couldn't

  When I tried to take this string into a variable I am getting an error.

  I know the work around is to put in double apostrophes, but this is live data.

  Is there a way you can do it?

  Thanks

  Vishy Dhara

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  May 29, 2008 at 1:37 am

  #821623

  So this is your original data:

  The' Big -- Blue Fox 'Jumped Over the fen'ce but couldn't

  Turn off quoted identifer and add a double quote at the beginning and end works for this example.

  [font="Courier New"]SET QUOTED_IDENTIFIER OFF

  declare @myVar varchar(100)

  set @myVar = "The' Big -- Blue Fox 'Jumped Over the fen'ce but couldn't"

  select @myVar as result

  result

  ----------------------------------------------------------

  The' Big -- Blue Fox 'Jumped Over the fen'ce but couldn't

  (1 row(s) affected)[/font]

  /Kenneth

• Viswanath S. Dhara

  SSC Veteran

  Points: 234

  More actions

  May 29, 2008 at 6:23 am

  #821712

  Thanks a lot!