How to insert & as a column value in a table

Introduction:



This post is very simple, but usefull, as when I tried to find this thing on google, I couldnt get any page, may be I didnt search properly, but I wanted to share how we can insert & as a column value. This post is very useful if you are inserting thousands of records and all have & in one of the column values. As if you will try to run in SQL*PLUS, it would be asking you the value for a variable preceeding &.



Reolution:



We did differnet testings, and finally was able to insert the & as a column value, following are the testings and results.



1- created an empty table with one field as text varchar2(60).



test-sql>create table testtable (text varchar2(60));
Table created.



2- different tries to insert the &, last one is successfull.



a- test-sql>insert into testtable values(' this is & record');
Enter value for record: &
old 1: insert into testtable values(' this is & record')
new 1: insert into testtable values(' this is &')

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &



b- test-sql>insert into testtable values(' this is '&' record');
insert into testtable values(' this is '&' record')
*
ERROR at line 1:
ORA-00917: missing comma



c- test-sql>insert into testtable values(' this is "&" record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record



d- test-sql>insert into testtable values(' this is '''&''' record');
insert into testtable values(' this is '''&''' record')
*
ERROR at line 1:
ORA-00917: missing comma



e- test-sql> insert into testtable values (' this is ' '&' ' record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record
this is & record