oracle sql - How to I escape the result text

Question

If I have the following;

SQL> create table test (field1 varchar(25));

Table created.

SQL> insert into test values ('Ric''s test');

1 row created.

SQL> select * from test; 

FIELD1
-------------------------
Ric's test

How can I change the select statement so that the result is,

Ric''s test

so the ' is escaped

Long version, I am writing a statement that will extract the table into sql statements, for example;

create table test2 (field1 varchar(25),
 field2 varchar(25),
 field3 varchar(25),
 field4 varchar(25),
 field5 varchar(25)
);
insert into test2 values ('one','two','three''s','four','five');


select 'INSERT INTO othertable (
 field1,field2,field3,field4,field5) values
 ('''||field1||''', '''||field2||''', '''||field3||''',
 '''||field4||''', '''||field5||''');' as val from test2;

The result of the last select is;

INSERT INTO othertable (
field1,field2,field3,field4,field5) values
('one', 'two', 'three's',
'four', 'five');

which is redirected into a .sql file, which can then be run later. However, as you can see the 'three's' is incorrect, and i need to escape it.

Any suggestions really appreciated.

Answer 1

You can use the Q quote syntax:

SQL> insert into test values (q'[Ric''s test]');

1 row created.

SQL> select * from test;

FIELD1
-------------------------
Ric''s test

This wil say to Oracle to interpret the string exactly as is; I used [], but you can use quite anything you want: [], {}, <>, ...

Answer 2

select replace(field1, '''', '''''') from test