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   amp  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

User · Answer

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

User · Answer

This works as well   select   from mde product where cfn    A3D  amp  R01   you define  amp  as literal by enclosing is with double qoutes   amp   in the string

User · Answer

straight from oracle sql fundamentals book   SET DEFINE OFF select  Coda  amp  Sid  from dual  SET DEFINE ON   how would one escape it without setting define

User · Answer

Instead of   node name    Geometric Vectors   amp  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   amp   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

User · Answer

You can use  set define off   Using this it won t prompt for the input

User · Answer

--SUBSTITUTION VARIABLES -- these variables are used to store values TEMPorarily  -- The values can be stored temporarily through -- Single Ampersand   amp   -- Double Ampersand  amp  amp   -- 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   amp stud Id    amp First Name     amp Last Name     amp Dob    amp fees    amp Gender    --Using double ampersand substitution variable INSERT INTO Student  Stud id First Name  Last Name Dob Fees Gender  VALUES   amp stud Id    amp First Name     amp Last Name     amp Dob    amp  amp fees   amp gender

User · Answer

REPLACE  lt your xml column gt    amp   chr 38    amp

User · Answer

In order to escape  amp  you can try following ways -   set scan off set define off set escape on  then replace  amp  by  amp  replace  amp  by  amp  amp    One of them should work atleast   Additionally if you are using PL SQL developer then there is  amp  icon in bottom of sql window please go there and disable it  Note in older version this option is not present   Also make sure set define off is written in very beginning of the script

User · Answer

I wrote a regex to help find and replace   amp   within an INSERT  I hope that this helps someone   The trick was to make sure that the   amp   was with other text   Find                  amp      amp                 Replace     1     chr 38       3

User · Answer

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     amp     World    Hello  amp  World  You re welcome

User · Answer

set escape on     node name    Geometric Vectors   amp  Matrices        or alternatively   set define off     node name    Geometric Vectors  amp  Matrices        The first allows you to use the backslash to escape the  amp    The second turns off  amp   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

[sql] Escaping ampersand character in SQL string

Examples related to sql

Examples related to oracle

Examples related to escaping

Examples related to sqlplus