Testing column value containing special Characters in oracle sql where clause prompts for a value

User_UH6TP

I have a where clause that uses the value of a column that contains a special character as a criteria in an oracle sql query. when I run the query, it prompts for me to enter the value after the special character.

E.g

where TAG_KEY = 'SELL_&_TUBE'

when I run the query. It prompts that I should enter the value of _TUBE.

SELL_$_TUBE is one of the values in TAG_KEY COLUMN.

Please I need help in resolving this,

I am using Oracle developer

User_H3J7U

Some programs use SQL*Plus substitution variables identified by & char. Read the SQL*Plus User's Guide about SET DEFINE and other input control options.

Frank Kulash

Hi,

Your front end is looking for substitution variables, whose names start with &. If you're not using substitution variables in your query, you can turn off that feature before running the query. In SQL*Plus, the way to do that is to say

SET DEFINE OFF

(From now on, I'm assuming SQL*Plus is your front end.) After running that command, & is a normal character with no special meaning.

If you want to turn on the substitution variable feature again after running the query, then say

SET DEFINE ON

If you are using substitution variables, then there are several ways to get around the problem. Perhaps the simplest is to change the special character that marks substitution variables, e.g.

SET DEFINE ~

After running this SQL*Plus command, substitution variables will have name like ~NUM_ROWS, and & will not have any special meaning (or cause any problems).

KayK

Hi UH6,

welcome to the communities.

Try this to disable the substitution

set define off
select * 
  from emp
 where ename = 'SELL_&_TUBE';

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SET-system-variable-summary.html#GUID-440C19CB-297E-4630-894C-FA7939153CD6

or something like this mask the define character this

select * 
  from emp
 where ename = 'SELL_'||'&'||'_TUBE';

or escape the ampersand

set escape \
select * 
  from emp
 where ename = 'SELL_\&_TUBE'; 

regards

Kay

btw please choose a more readable name for your avatar. You're more than a number. Have a look at this: Update Your Community Display Name and Avatar!