Escape character for &

[bang_dba]

Hi,

I want to insert & into a column; Can somebody tell me what is the Escape char for the same.

[davey23uk]

well if you are using sqlplus, set define off at the start

but you really should provide all the details then you might get a better answer, you have posted enough questions here to know that.

[thomasp]

Like Davey said, in sqlplus, you can use set define off

Otherwise, you can use '\' character to escape.

eg:

insert into test_table values ('C\&G');

[slimdave]

Like Davey said, in sqlplus, you can use set define off

Otherwise, you can use '\' character to escape.

eg:

insert into test_table values ('C\&G');

Doesn't work for me

SQL> create table g (col1 varchar2(30));

Table created.

SQL> insert into g values ('C\&G');
Enter value for g: ?
old   1: insert into g values ('C\&G')
new   1: insert into g values ('C\?')

1 row created.

SQL> select * from g;

COL1
------------------------------
C\?

[thomasp]

Sorry for not being specific. It does not work in sqlplus.
My otherwise meant outside of sqlplus. the '\' would work.

[WilliamR]

Substitution variables are a SQL*Plus feature, although other tools such as TOAD and PL/SQL Developer may implement something similar.

In SQL*Plus the '&' character is just the default but can be set to anything you like, or the feature can be disabled as mentioned above. Similarly an escape character can be defined using SET ESCAPE, '\' being the default character.

Outside of tools that provide a substitution variable syntax, the '&' character has no special meaning in SQL or PL/SQL.