Replacing a pattern match using sqlplus
Hello,
I have a need to replace a substring in a CLOB with a new pattern.For example if the CLOB's tree_data has
4.url 102 /track?linkid=1005297&add=1&url=http://www.amazon.com/exec/obidos/redirect-home/abc-favorites-20
I'd like to change the patternabc-favorites-20 toabc-favorites-new-20.
SQL> describe abc_favorites;
Name Null?Type
-- -- -
ID NUMBER
USER_IDNOT NULL NUMBER(19)
MODIFIED_DATE DATE
TREE_DATACLOB
How would do about doing this?
Any suggestions would be great.
thank you,
MH
[624 byte] By [
mhdevid9a] at [2007-10-3 2:47:13]
Presumably by 'pattern' you actually mean 'text'. If you actually meant something like 'regex pattern' then you are going to have a lot of difficulty. So going with 'text'...
Look for a replace method. I would guess it doesn't exist, but if it does then use it.
Otherwise you look for the start index of the string that should be replaced. Then you take every before it, everything after it and the replace string and put it together. PL/SQL allows all that with the string methods it has.
If you are using Oracle 10g it has a REGEXP_REPLACE method that can be used for global replacement that might work for you.
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions115.htm
