The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

Silly question really, I'd like to use the update statement with a long varchar but the string I want to update it to might have apostrophes in it, I'm pretty sure the only way to do this is to double up the apostrophes so Syabse sees them as apostrophes rather than the end of a string. Is this right or is there something clever that I've missed ?

Thanks. Daz.


asked 01 Aug '10, 17:58

Daz%20Liquid's gravatar image

Daz Liquid
accept rate: 28%

If the string value is already stored inside a SQL variable or a host variable, then nothing needs to be done; embedded apostrophes will be processed as-is because the string value is NOT being parsed as a string literal surrounded by quotes.

Consider a text file C:\temp\stringfile.txt containing this data:

Fred's not here.

Here's some code that doesn't need special handling of the apostrophe:

CREATE TABLE stringtable (
   stringcolumn LONG VARCHAR NOT NULL );

SET @stringvar = xp_read_file ( 'C:\\temp\\stringfile.txt' );  
INSERT stringtable VALUES ( 1, @stringvar );
SELECT * FROM stringtable;

Here's an example of a string literal that needs parsing and therefore needs the embedded apostrophe to be doubled up:

INSERT stringtable VALUES ( 2, 'What''s the problem?' );

If you are using EXECUTE IMMEDIATE, it depends on how you are constructing the SQL statement whether apostrophes need doubling; if so, you can use the REPLACE function.

permanent link

answered 01 Aug '10, 18:15

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

edited 02 Aug '10, 10:13


@Breck, I think you meant to say use the "REPLACE" function, not the "REPEAT" function. E.g. Replace( @s, '''', '''''' ) - that is saying replace all single apostrophies with two apostrophies.

(01 Aug '10, 19:47) Mark Culp

...ahhh, indeed, and it has been so edited :)

(02 Aug '10, 10:14) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 Aug '10, 17:58

question was seen: 1,005 times

last updated: 02 Aug '10, 10:13

Related questions