| replace please help [message #371395] |
Thu, 12 October 2000 10:50  |
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 #371400 is a reply to message #371395] |
Fri, 13 October 2000 06:39  |
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 :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
