Home » SQL & PL/SQL » SQL & PL/SQL » list of characters
list of characters [message #263495] Thu, 30 August 2007 03:35 Go to next message
palmer18
Messages: 6
Registered: June 2007
Junior Member
Hi, I need to replace characters ?,+,% in one column for _

CREATE OR REPLACE PROCEDURE replace1 IS
tm char;
BEGIN

FOR tm IN ('?', '+', '%') LOOP
UPDATE Employees
SET owner_name = Replace(owner_name, tm, '_');
END LOOP;

but this doesn´t work
can you help me with this please ?
Re: list of characters [message #263504 is a reply to message #263495] Thu, 30 August 2007 03:51 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Read about cursor for loop.
Re: list of characters [message #263507 is a reply to message #263495] Thu, 30 August 2007 03:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

A demo of your requirement.
SQL> select * from employees;

A
----------
HAI_

SQL> insert into employees values('OK?');

1 row created.

SQL> insert into employees values('OK+?');

1 row created.

SQL> commit;

Commit complete.
SQL> CREATE OR REPLACE PROCEDURE replace1 IS
BEGIN
FOR tm IN (select a from employees) LOOP
UPDATE Employees
SET a = Replace(a, '+', '_');
Update employees
SET a = Replace(a, '?', '_');
END LOOP;
end;  2    3    4    5    6    7    8    9
 10  /

Procedure created.

SQL> exec replace1;

PL/SQL procedure successfully completed.

SQL> select * from employees;

A
----------
HAI_
OK_
OK__
Re: list of characters [message #263513 is a reply to message #263495] Thu, 30 August 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
translate (mycol,'?+%','___')

Regards
Michel
Re: list of characters [message #263514 is a reply to message #263507] Thu, 30 August 2007 04:08 Go to previous messageGo to next message
palmer18
Messages: 6
Registered: June 2007
Junior Member
is it possible to use something like numlist ? but for characters

like this:

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE ....


END;
Re: list of characters [message #263517 is a reply to message #263513] Thu, 30 August 2007 04:16 Go to previous messageGo to next message
palmer18
Messages: 6
Registered: June 2007
Junior Member
translate function works well, thank you

translate (mycol,'?+%','___')
Re: list of characters [message #263520 is a reply to message #263514] Thu, 30 August 2007 04:19 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Try with this:

CREATE OR REPLACE PROCEDURE replace1 IS

	cursor c1 is
		(select '?' replace_char from dual
		union
		select '+' replace_char from dual
		union
		select '%' replace_char from dual);
BEGIN
FOR tm in c1 LOOP
UPDATE Employees
SET owner_name = Replace(owner_name, tm.replace_char, '_');
END LOOP;
end;

[Updated on: Thu, 30 August 2007 04:19]

Report message to a moderator

Re: list of characters [message #263525 is a reply to message #263520] Thu, 30 August 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already said, translate works with only 1 update.

Regards
Michel
Re: list of characters [message #263526 is a reply to message #263520] Thu, 30 August 2007 04:25 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd use a single update statement with translate. No for loops = better performance.

Edit: thanks for confirming, Michel.

MHE

[Updated on: Thu, 30 August 2007 04:25]

Report message to a moderator

Previous Topic: Direct Path Loading
Next Topic: help string to table (this is not yrgent at all, so please take your time)
Goto Forum:
  


Current Time: Thu Dec 08 04:17:01 CST 2016

Total time taken to generate the page: 0.08495 seconds