Home » RDBMS Server » Server Administration » replace please help
replace please help [message #371395] Thu, 12 October 2000 10:50 Go to next message
mokrane
Messages: 4
Registered: October 2000
Junior Member
Hi,
I have a string as follows :
ABCDEFGACRDHIGKAVCD....
I have to replace the string A$$D to XXXX on all the occurence
That is XXXXEFGXXXHIGKXXX....
I tried with replace & substring I did not succeed.
If anybody has done this kind of manipulation,
please help me
Thanks
Mokrane
Re: replace please help [message #371398 is a reply to message #371395] Fri, 13 October 2000 06:11 Go to previous messageGo to next message
Prem
Messages: 79
Registered: August 1998
Member
Is it ok if you can use a user defined function in your SQL or do you have to use only the oracle supplied functions?

Prem :)
Re: replace please help [message #371399 is a reply to message #371395] Fri, 13 October 2000 06:34 Go to previous messageGo to next message
mokrane
Messages: 4
Registered: October 2000
Junior Member
Hi Prem,
I can use user difined function in my sql
Mokrane
Re: replace please help [message #371400 is a reply to message #371395] Fri, 13 October 2000 06:39 Go to previous message
Prem
Messages: 79
Registered: August 1998
Member
Mokrane,

Then use this function

CREATE OR REPLACE FUNCTION RP (PVAR VARCHAR2) RETURN VARCHAR2 IS
TVAR VARCHAR2(2000) := PVAR;
ALOC NUMBER := 1;
DLOC NUMBER := 1;
BEGIN
LOOP
ALOC := INSTR(TVAR, 'A', ALOC);
EXIT WHEN ALOC = 0;
DLOC := INSTR(TVAR, 'D', ALOC);
EXIT WHEN DLOC = 0;
IF DLOC - ALOC = 3 THEN
TVAR := SUBSTR(TVAR, 1, ALOC-1)||'XXXX'||SUBSTR(TVAR, ALOC+4);
END IF;
ALOC := ALOC + 1;
END LOOP;
RETURN TVAR;
END;
/

assume you want to use it to convert the employee names (i can imagine why someone might do that)

SELECT RP(ENAME) FROM EMP;

Also make sure the parameter you pass is not null, or handle it in the function. Using NVL might be better.

SELECT RP(NVL(ENAME, 'NONE')) ENAME FROM EMP

hth

Prem :)
Previous Topic: QUERY..help..:o)
Next Topic: Cursor over two schemas?
Goto Forum:
  


Current Time: Thu Mar 28 13:03:46 CDT 2024