OraFAQ Forum: SQL & PL/SQL » replacing & by &

Home » SQL & PL/SQL » SQL & PL/SQL » replacing & by &

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

replacing & by & [message #119865]

Mon, 16 May 2005 08:23

Salim_S
Messages: 1
Registered: May 2005

Junior Member

There is a command in oracle that can replace a character by a string. I need to replace all the '&' by '&'.
E.g. I want to replace "XYZ & CO. Ltd" by "XYX & CO. Ltd". The sql command is : select replace(custname, '&','&') from mytable. By so doing it is prompting to enter a value for amp. Can any body help?
Thx

Report message to a moderator

Re: replacing & by & [message #119875 is a reply to message #119865]

Mon, 16 May 2005 08:55

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

SQL> COLUMN REPLACED FORMAT A25
SQL> SELECT * FROM mytable;

CUSTNAME
--------------------
XYZ & CO. Ltd

SQL> SELECT custname, REPLACE(custname,'&', '&'||'amp;') REPLACED
  2  FROM mytable;

CUSTNAME             REPLACED
-------------------- -------------------------
XYZ & CO. Ltd        XYZ &amp;  CO. Ltd

Regards.

[Updated on: Mon, 16 May 2005 08:59]

Report message to a moderator

Re: replacing & by & [message #119921 is a reply to message #119865]

Mon, 16 May 2005 13:23

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

You have a couple of options here.

You could use SET DEFINE OFF in SQL*Plus to turn off variable substitution altogether.

You could escape the ampersands in your commands by using SET ESCAPE ON:

SQL> SET ESCAPE ON
SQL> CREATE TABLE t (customer_name VARCHAR2(20));
 
Table created.
 
SQL> INSERT INTO t VALUES ('XYZ \& CO. Ltd');
 
1 row created.
 
SQL> SELECT REPLACE(t.customer_name,'\&','\&amp;') new_cust_name FROM t
  2  /
 
NEW_CUST_NAME
--------------------------------------------------------------------------------
XYZ &amp; CO. Ltd
 
SQL>

You could use CHR(38) instead of "&".

SQL> SELECT REPLACE(t.customer_name,CHR(38),CHR(38) || 'amp;') new_cust_name FROM t
  2  /
 
NEW_CUST_NAME
--------------------------------------------------------------------------------
XYZ &amp; CO. Ltd
 
SQL>

But the best thing to do would probably be to use the PL/SQL Web Toolkit's escape_sc function:

SQL> SELECT htf.escape_sc(t.customer_name) escaped_cust_name FROM t
  2  /

ESCAPED_CUST_NAME
--------------------------------------------------------------------------------
XYZ &amp; CO. Ltd

SQL>

Report message to a moderator

Re: replacing & by & [message #119936 is a reply to message #119865]

Mon, 16 May 2005 15:48

Steve Corey
Messages: 336
Registered: February 2005
Location: RI

Senior Member

In Oracle SQL, the & represents a lexical parameter or a substituted value. You have to enclose it in either single or double quotes to avoid being prompted for a value as saadatmahad indicated. Art also provided a solution in SQL*Plus context, but will not work if you are to be running this solution in a package or program unit or trigger context.

Steve

Report message to a moderator

Re: replacing & by & [message #120442 is a reply to message #119865]

Thu, 19 May 2005 15:32

wejder
Messages: 7
Registered: May 2005
Location: Warsaw

Junior Member

or u can use 2 times replace function Wink

once i wrote something like this for my own use

select 
   'update site set site_name = ''' || 
    replace(
            replace(
                    replace(
	  		  replace(site_name,'&','')
  		  ,'amp;','')
		  ,'quot;','')
                  ,'#338;','') 
       || ''' where id_site = ' || id_site || ';' from sites.site order by id_site

regards

[Updated on: Thu, 19 May 2005 15:33]

Report message to a moderator

Previous Topic:	RE:dbms_alert
Next Topic:	query question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 23:27:10 CDT 2025