Forum Stats

  • 3,770,132 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

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

User_UH6TP
User_UH6TP Member Posts: 3 Green Ribbon

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

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    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
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    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
    KayK Member Posts: 1,685 Bronze Crown
    edited Jun 18, 2021 10:58AM

    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!