Query Help - String in where clause has & character

Question

I am running an SQL (Oracle) statement like that

select * from table 
where table_id in ('265&310', '266&320')

While running through TOAD, it consider & as some variable placeholder and it asks for its value. If it was for 1-2 place holders then I could have set it in TOAD but the in clause has like 200 of strings.

How to put this query?

I want to export the DATASET as SQL INSERT statement, so I can't use this in SQL-PLUS.

Sirenasirenic

Answer 1

In TOAD, you can disable the prompt for substitution variables from the options dialog:

You need to uncheck: View –> Toad Options –> Execute/Compile –> Prompt for Substitution variables.

Kinata

Answer 2

SET DEFINE OFF;

Will work to turn the prompting for variable off..

or

SET ESCAPE ON;
SELECT 'blah \& blah' AS DES FROM DUAL;

Fistula

is there any optino to set define off or set define ~ in TOAD? – Sirenasirenic

That works when running as a script, but TOAD uses it's own parser for running individual statments. Fortunately, you can disable substitution from the Options menu, as I showed in my answer. – Kinata

Answer 3

You can escape the ampersand character by using concatenation, like this:

select * from table 
where table_id in ('265' || '&' || '310', '266' || '&' || '320')

Heptangular

this is gona be lots of effort :( – Sirenasirenic

Another option is to do as Randy suggested and, for example, to set the DEFINE character to something else or off altogether. SET DEFINE ~; – Heptangular