Solved

Disable special character & in Oracle sql statement

Posted on 2009-04-02
3
956 Views
Last Modified: 2013-12-18
If you issue
SQL> select '&1' from dual;
Enter value for 1: dummy
old   1: select '&1' from dual
new   1: select 'dummy' from dual
dummy
However, I would like to get:
&2
instead of dummy, so disable the special charcter & in this sql statement.
0
Comment
Question by:jl66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 24051508
SQL> set define off

then run your query





0
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 300 total points
ID: 24052149
You can do the following alternative ways as well:
SELECT chr(ascii('&')) || '1' FROM dual;
Try also:
SELECT "&" || '1' FROM dual;

Just like single quote ' is specific escape character for ' i.e. you will use it as:
SELECT 'gain''s' FROM dual;
Back slash (\) is the generic escape character in Oracle.
0
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 300 total points
ID: 24052185
You can use REPLACE and TRANSLATE functions to substitute a harmless and unused character in place of & and then substitute back while using the result OR if used in WHERE condition, then compare with constant/literal value also containing substituted character.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question