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.
answered
01 Aug '10, 18:15
Breck Carter
32.5k●540●724●1050
accept rate:
20%