RE: Ampersands in data - this might be useful?

Thanks for the update!
i ran into this same situation when attempting to construct values clause for my INSERT Statements...the solution which worked for me used the CHR(dec) e.g.
INSERT INTO FLOYD_SONGS VALUES('Us '||CHR(38)||' Them');

select * from FLOYD_SONGS
'Us & Them'

Good Stuff!
Martin Gainty 
______________________________________________ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.


To: [email protected]
From: [email protected]
Date: Thu, 21 Jun 2012 10:00:39 +0100
Subject: [toad] Ampersands in data - this might be useful?
















 



  


    
      
      
      Morning all,



there have been a number of "problems" in the past reported here about 

inserting (or updating) data which contains an ampersand. The following 

might be useful?



SQL> create table amp(a varchar2(100));

Table created.



SQL> insert into amp values ('Us & Them');

Enter value for them: XXXX

1 row created.



SQL> insert into amp values ('Us ' || '&' || ' Them');

1 row created.



SQL> insert into amp values ('Us &' || ' Them');

1 row created.



SQL> select * from amp;



A

---------

Us XXXX

Us & Them

Us & Them



It appears that you only get prompted for a substitution if the 

ampersand is in the middle of the string. If it is on its own - as per 

the second example, or at the end of a/the string - example 3 - then it 

is not considered a substitution prompt and is inserted or updated as is.



I know you can change the define character, but it's always a good thing 

to have more than one way to skin a cat, proverbial or otherwise!



Works for update statements as well:



SQL> update amp set a = 'Us '||'&'||' Them'

   2  where a = 'Us XXXX';

1 row updated.



SQL> select * from amp;



A

---------

Us & Them

Us & Them

Us & Them



And selects/Deletes:



SQL> select a from amp

   2  where a =  'Us '||'&'||' Them';



A

---------

Us & Them

Us & Them

Us & Them



SQL> delete from amp

   2  where a =	'Us '||'&'||' Them';



3 rows deleted.



-- 

Cheers,

Norm. [TeamT]