How do I update a long varchar with a string containing apostrophes

[former_SQLA_member1694874]

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

[Breck_Carter · Accepted solution]

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.

[MarkCulp · Accepted solution]

@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.

[Breck_Carter · Accepted solution]

...ahhh, indeed, and it has been so edited 🙂