How to insert a string which contains an “&”

by

I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far.

Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem – when it appears, it is expected to be part of a variable name.

SET DEFINE OFF will stop sqlplus interpreting & this way.

But what if you need to use sqlplus variables and literal & characters?

  • You need SET DEFINE ON to make variables work
  • And SET ESCAPE ON to escape uses of &.

e.g.

set define on
set escape on

define myvar=/forth

select 'back\\ \& &myvar' as swing from dual;

Produces:

old   1: select 'back\\ \& &myvar' from dual
new   1: select 'back\ & /forth' from dual

SWING
--------------
back\ & /forth

If you want to use a different escape character:

set define on
set escape '#'

define myvar=/forth

select 'back\ #& &myvar' as swing from dual;

When you set a specific escape character, you may see ‘SP2-0272: escape character cannot be alphanumeric or whitespace’. This probably means you already have the escape character defined, and things get horribly self-referential. The clean way of avoiding this problem is to set escape off first:

set escape off
set escape '#'

More Related Contents:

  1. Escaping ampersand character in SQL string
  2. When do I need to use a semicolon vs a slash in Oracle SQL?
  3. How do I spool to a CSV formatted file using SQLPLUS?
  4. How do I ignore ampersands in a SQL script running from SQL Plus?
  5. How to handle a single quote in Oracle SQL
  6. Oracle SQL escape character (for a ‘&’)
  7. How to get Oracle create table statement in SQL*Plus
  8. How to kill a running SELECT statement
  9. sqlplus statement from command line
  10. The most efficient way to UPPER CASE two columns in a single query [closed]
  11. Current date in Oracle Db
  12. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  13. Case insensitive searching in Oracle
  14. Get value based on max of a different column grouped by another column [duplicate]
  15. How to find the employee with the second highest salary?
  16. Oracle: What does `(+)` do in a WHERE clause?
  17. Concatenate results from a SQL query in Oracle
  18. Generate a range of dates using SQL
  19. SQL recursive query on self referencing table (Oracle)
  20. PLS-00428: an INTO clause is expected in this SELECT statement
  21. Equivalent function for DATEADD() in Oracle
  22. How do I convert a “legacy” left outer join statement in Oracle?
  23. How to Alter Constraint
  24. Group by with CLOB in select-statement
  25. calculate hours based on business hours in Oracle SQL
  26. Oracle/SQL: Why does query “SELECT * FROM records WHERE rownum >= 5 AND rownum
  27. Connecting to Oracle Database using Sql Server Integration Services
  28. ORA-00933: SQL command not properly ended
  29. Update a table with data from another table
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment