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 ?
asked 01 Aug '10, 17:58
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.