How to avoid variable substitution in Oracle SQL Developer with 'trinidad & tobago'
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';