how to tell oracle sql treat & as data not as bing variable

Question

I have to update columns in a table with data. And in one of column's data has & in it like( 'Salt & Pepper').

So when I run the update statement, it is prompting me to enter the value.

But in my case, it should not ask, it should insert the data 'Salt & Pepper' into the table's column.

UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;

How can I do that?

Answer 1

In SQL*Plus, it is SET DEFINE OFF

SQL> UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;
Enter value for pepper:
old   1: UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000
new   1: UPDATE dishes SET ingredient = 'Salt ' WHERE dish_id = 5000

1 row updated.

But:

SQL> set def off
SQL> UPDATE dishes SET ingredient = 'Salt & Pepper' WHERE dish_id = 5000;

1 row updated.

SQL>

Tool you use might have different option, but - generally speaking - it comes to the same thing.

Answer 2

use SET ESCAPE: SET ESC[APE] {OFF | ON | escape_char}

so:

SET ESCAPE ON
SET ESCAPE "\"
UPDATE dishes SET ingredient = 'Salt \& Pepper' WHERE dish_id = 5000;

should do the trick, I think.