Escaping ampersand character in SQL string

Question

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);

i don't think you need the quote around the escape ('\') character — mcalex, Oct 18 '12 at 18:33

score 266 · Accepted Answer · answered Oct 18 '12 at 18:42

266

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.

answered Oct 18 '12 at 18:42

Imre Greilich

3,272
1
15
7

51

Instead of the unintuitive `chr(38)`, you can do `node_name = 'Geometric Vectors &' || ' Matrices'` – Jay Sullivan Jul 19 '13 at 13:29
4

For other poor confused souls alike to myself, note that it is `chr(38)`, *not* `char(38)`. – xdhmoore Aug 29 '18 at 16:34
Note that `&` doesn't need to be escaped in MySQL. Also MySQL doesn't have the function `CHR()`. – asmaier Jun 18 '20 at 11:24

Alex Poole · Answer 2 · 2012-10-18T18:33:55.980

134

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.

edited Oct 18 '12 at 18:33

answered Oct 18 '12 at 18:28

Alex Poole

183,384
11
179
318

4

The escape *character* is set to `\ ` by default, but the boolean parameter `escape` is set to OFF by default. So the OP may not need to set the escape character, but he/she needs to at least `SET ESCAPE ON` for that to work. Or, of course, use any of the other, better solutions. – Feb 25 '17 at 13:27

score 52 · Answer 3 · answered Oct 18 '12 at 18:27

52

You can use

set define off

Using this it won't prompt for the input

answered Oct 18 '12 at 18:27

Ankur

12,676
7
37
67

score 36 · Answer 4 · edited Dec 05 '13 at 19:53

36

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.

edited Dec 05 '13 at 19:53

jszobody

28,495
6
61
72

answered Dec 05 '13 at 19:32

Roman

369
3
2

2

The addition of SET DEFINE ON is helpful. – KSev Feb 21 '14 at 17:03
@Roman Without define you could use as well: select 'Coda ''&'' Sid' from dual; (double single quotes) – antcalvente Jul 04 '19 at 15:50

AConsumer · Answer 5 · 2021-10-22T04:52:18.127

15

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.

edited Oct 22 '21 at 04:52

answered Oct 15 '18 at 10:55

AConsumer

2,461
2
25
33

1

In step 3, shouldn’t that be `\&` ? – Manngo Oct 24 '20 at 22:08
updated answer @Manngo – AConsumer Oct 22 '21 at 04:52

David Balažic · Answer 6 · 2018-06-14T14:04:18.313

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.

score 1 · Answer 7 · edited Feb 08 '17 at 13:40

1

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.

edited Feb 08 '17 at 13:40

Stasel

1,298
1
13
26

answered Feb 08 '17 at 12:51

copmac

37
1

1

If you do that, the the double-quotes will become part of the string, like this: `A3D"&"R01` – David Balažic Jan 15 '18 at 14:12

score 0 · Answer 8 · edited May 26 '16 at 10:31

0

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

edited May 26 '16 at 10:31

dur

15,689
25
79
125

answered May 26 '16 at 09:15

user6385350

11

score 0 · Answer 9 · edited Jan 14 '20 at 20:04

0

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”

edited Jan 14 '20 at 20:04

j.a.estevan

3,057
18
32

answered Jan 14 '20 at 19:07

that_roy

59
9

score 0 · Answer 10 · answered Dec 07 '20 at 06:34

--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');

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. — Tyler2P, Dec 07 '20 at 10:26

score -1 · Answer 11 · answered Nov 26 '20 at 22:37

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 ;)

Escaping ampersand character in SQL string

11 Answers11

Linked

Related