[sql] How to insert a string which contains an "&"

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 '#'

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to escaping

Uses for the '"' entity in HTML Javascript - How to show escape characters in a string? How to print a single backslash? How to escape special characters of a string with single backslashes Saving utf-8 texts with json.dumps as UTF8, not as \u escape sequence Properly escape a double quote in CSV How to Git stash pop specific stash in 1.8.3? In Java, should I escape a single quotation mark (') in String (double quoted)? How do I escape a single quote ( ' ) in JavaScript? Which characters need to be escaped when using Bash?

Examples related to sqlplus

When or Why to use a "SET DEFINE OFF" in Oracle Database SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory How do I resolve this "ORA-01109: database not open" error? How to echo text during SQL script execution in SQLPLUS PL/SQL ORA-01422: exact fetch returns more than requested number of rows how to pass variable from shell script to sqlplus Connect to Oracle DB using sqlplus sqlplus how to find details of the currently connected database session How to output oracle sql result into a file in windows?