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.

v10.0.1.4075

asked 01 Aug '10, 17:58

Daz%20Liquid's gravatar image

Daz Liquid
861182338
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 (
   pkey         INTEGER NOT NULL PRIMARY KEY,
   stringcolumn LONG VARCHAR NOT NULL );

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

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
27.4k424585838
accept rate: 21%

edited 02 Aug '10, 10:13

3

@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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×1

question asked: 01 Aug '10, 17:58

question was seen: 916 times

last updated: 02 Aug '10, 10:13

Related questions