search and replace with in a field

[mbrust]

Hi all.

I have a table with a field containing entries like "

http://test/user..."

I need to replace the "test" by an IP-Adress.

Is there any chance to do this automatically?

Thanks
Mike

 

[ociruj]

hello,

you can try:

update <table_name>
set <column_name> = replace(<column_name>, &quot;test&quot;, &quot;<ip address>&quot;
...

hope this helps...

regards

 

[mbrust]

thanks for the hint. Unfortunately, this is not a varchar field, so replace is not supported here
&quot;ORA-00932: inconsistent datatypes&quot;

 

[Turkbear]

HI,
What datatype is it?

 

[mbrust]

the datatype is LONG

 

[djbjr]

How about this

CREATE TABLE <STAGE_TABLE>
AS SELECT * FROM <TABLE_NAME>

TRUNCATE TABLE <TABLE_NAME>

INSERT INTO <TABLE_NAME>
SELECT
COL1 ... etc,
CASE WHEN <COL_NAME> LIKE '%TEST%'
THEN '

HTTP://'||<ip_address>||substr(<col_name>,12)

ELSE <col_name>
END AS <COLUMN_NAME>
;

If your column always starts with

http://test

-- and thats all you want to remove, using this case statement will work

 

[mbrust]

well, the content I am looking &quot;test&quot; is part of this field, like &quot; 123456abc

http://test/user&quot;

and I do need to keep everything in front of &quot;test&quot; and everything behind &quot;test&quot;

it's more like a global search and replace part-string.
in fact, it does never start with &quot;

http://test&quot;.....

:-((

 

[Turkbear]

Hi,
Just one more annoying question:
Why LONG ( the most unfriendly datatype in Oracle - except for LONG RAW )..

If you can convert it to varchar2 you will solve your problem...( and others you haven't encountered yet)

 

[sem]

Once you've fetched this field content into varchar2 field, the task becomes very simple:

substr(<field>, 1,
instr(<field>, '

http://test/')

+ 6)
||<ip>
||substr(<field>,
instr(<field>,'

http://test/')+

11)


Regards, Dima

 

[mbrust]

ok,

converting seems to be the most valuable action.
Thanks for this hint and the code sample

Cheers
Mike