Escaping ampersand character in SQL string

Sql Problem Overview

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm uncertain as to what exactly my problem is.

Set escape '\'
    select * from V1144engine.T_nodes where node_id in(
    select node2_id from V1144engine.T_edges where node1_id in(
    select node2_id from V1144engine.T_edges where node1_id in(
    select node2_id from V1144engine.T_edges where node1_id = 
      (select node_id from V1144engine.T_nodes where node_name = 'Geometric Vectors \& Matrices')))
    and edge_type_id = 1)
    and node_type_id = 1
    and node_id in (
    select node2_id from V1144engine.T_edges where node1_id =
      (select node_id from V1144engine.T_nodes where node_name = 'Algebra II')
    and edge_type_id = 2);

Although this has a similar solution to this question, the problems are posed very differently. They may end up having the same solution, but that does not mean that the questions are the same.

Sql Solutions

Solution 1 - Sql

Instead of

node_name = 'Geometric Vectors \& Matrices'

use

node_name = 'Geometric Vectors ' || chr(38) || ' Matrices'

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked.

Another way could be using LIKE and an underline instead the '&' character:

node_name LIKE 'Geometric Vectors _ Matrices'

The chance that you'll find some other record too, which is different in only this one character, is quite low.

Solution 2 - Sql

Escape is set to \ by default, so you don't need to set it; but if you do, don't wrap it in quotes.

Ampersand is the SQL*Plus substitution variable marker; but you can change it, or more usefully in your case turn it off completely, with:

set define off

Then you don't need to bother escaping the value at all.

Solution 3 - Sql

You can use

set define off

Using this it won't prompt for the input

Solution 4 - Sql

straight from oracle sql fundamentals book

SET DEFINE OFF
select 'Coda & Sid' from dual;
SET DEFINE ON

how would one escape it without setting define.

Solution 5 - Sql

In order to escape & you can try following ways:-

set scan off
set define off
set escape on then replace & by\&
replace & by &&

One of them should work at least.

additionally if you are using PL/SQL developer then there is & icon in the bottom of SQL window please go there and disable it. Note in the older version this option is not present.

Also make sure set define off is written at the very beginning of the script.

Solution 6 - Sql

set escape on
... node_name = 'Geometric Vectors \& Matrices' ...

or alternatively:

set define off
... node_name = 'Geometric Vectors & Matrices' ...

The first allows you to use the backslash to escape the &.

The second turns off & "globally" (no need to add a backslash anywhere). You can turn it on again by set define on and from that line on the ampersands will get their special meaning back, so you can turn it off for some parts of the script and not for others.

Solution 7 - Sql

This works as well:

select * from mde_product where cfn = 'A3D"&"R01'

you define & as literal by enclosing is with double qoutes "&" in the string.

Solution 8 - Sql

REPLACE(<your xml column>,'&',chr(38)||'amp;')

Solution 9 - Sql

I wrote a regex to help find and replace "&" within an INSERT, I hope that this helps someone.

The trick was to make sure that the "&" was with other text.

Find “(\'[^\']*(?=\&))(\&)([^\']*\')”

Replace “$1' || chr(38) || '$3”

Solution 10 - Sql

--SUBSTITUTION VARIABLES
-- these variables are used to store values TEMPorarily.
-- The values can be stored temporarily through
-- Single Ampersand (&)
-- Double Ampersand(&&)
-- The single ampersand substitution variable applies for each instance when the
--SQL statement is created or executed.
-- The double ampersand substitution variable is applied for all instances until
--that SQL statement is existing.
INSERT INTO Student (Stud_id, First_Name, Last_Name, Dob, Fees, Gender)
VALUES (&stud_Id, '&First_Name' ,'&Last_Name', '&Dob', &fees, '&Gender');
--Using double ampersand substitution variable
INSERT INTO Student (Stud_id,First_Name, Last_Name,Dob,Fees,Gender)
VALUES (&stud_Id, '&First_Name' ,'&Last_Name', '&Dob', &&fees,'&gender');

Solution 11 - Sql

I know it sucks. None of the above things were really working, but I found a work around. Please be extra careful to use spaces between the apostrophes [ ' ], otherwise it will get escaped.

SELECT 'Hello ' '&' ' World';

Hello & World

You're welcome ;)

Content Type	Original Author	Original Content on Stackoverflow
Question	Slater Victoroff	View Question on Stackoverflow
Solution 1 - Sql	Imre Greilich	View Answer on Stackoverflow
Solution 2 - Sql	Alex Poole	View Answer on Stackoverflow
Solution 3 - Sql	Ankur	View Answer on Stackoverflow
Solution 4 - Sql	Roman	View Answer on Stackoverflow
Solution 5 - Sql	AConsumer	View Answer on Stackoverflow
Solution 6 - Sql	David Balažic	View Answer on Stackoverflow
Solution 7 - Sql	copmac	View Answer on Stackoverflow
Solution 8 - Sql	user6385350	View Answer on Stackoverflow
Solution 9 - Sql	that_roy	View Answer on Stackoverflow
Solution 10 - Sql	Mohan Reddy	View Answer on Stackoverflow
Solution 11 - Sql	Omar Valerio	View Answer on Stackoverflow