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]


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/