How to avoid variable substitution in Oracle SQL Developer

Call this before the query:

set define off;

Alternatively, hacky:

update t set country = 'Trinidad and Tobago' where country = 'trinidad &' || ' tobago';

From Tuning SQL*Plus:

SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.

In SQL*Plus putting SET DEFINE ? at the top of the script will normally solve this. Might work for Oracle SQL Developer as well.

this will work as you asked without CHAR(38):

update t set country = 'Trinidad and Tobago' where country = 'trinidad & '|| 'tobago';

create table table99(col1 varchar(40));
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
SELECT * FROM table99;

update table99 set col1 = 'Trinidad and Tobago' where col1 = 'Trinidad &'||'  Tobago';

If you use liquibase to run sql file, it will not give error. But for sql developer use this set define off; before ur sql sode

set scan off; Above command also works.