3

Possible Duplicate:
select * from table_name where column like '&nbsp'

How do you insert the & symbol as data in an Oracle database, using Toad? Example:

INSERT INTO <table_name> 
  (column)
VALUES 
  ('AT & T');

When executing script asking new value for T...

Community
  • 1
  • 1
Rose
  • 51
  • 1
  • 1
  • 5
  • It's because the `&` indicates BIND variable when Toad (or SQLPlus) executes the script. – OMG Ponies May 07 '11 at 14:56
  • possible duplicate of [select * from table_name where column like '&nbsp'](http://stackoverflow.com/questions/1957436/select-from-table-name-where-column-like-nbsp). Also: http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus. There are numerous duplicates: http://stackoverflow.com/search?q=[oracle]+%22set+define+off%22 – OMG Ponies May 07 '11 at 14:57

2 Answers2

9

Use this:

insert into my_table values ('AT &' || 'T');

You can also try using:

set define off;

In your environment so &Something won't be treated as if it is an input variable. But I don't know where to put that in TOAD.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Such a weird :-S Experienced the same issue and solved it in the same way, but may be there is an option to turn off placeholders look up? – zerkms May 07 '11 at 13:17
  • @zerkms: yes. `set define off` in **SQLPlus**. Don't know if it will work in **TOAD** though. But 100% agreed: it's weird. I guess it's still there for backward compatibility. This feature might be at least 25 years old. :-) – Pablo Santa Cruz May 07 '11 at 13:18
  • 1
    if define off then no need of || in this 'AT &' || 'T' – Vishwanath Dalvi May 07 '11 at 13:38
4

SET DEFINE OFF will stop sqlplus interpreting & this way.

this will also work

set define off;
insert into tablename values( 'AT & T');
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155