Update portions of Data in Oracle

Question

Hoping someone can assist with this data update using Oracle.

I have a situation where I have data/records that may look like this:

Table Name: IPDATA

Column Name with these records is called: info

Web Proxy (abc):ZZZ Gateway gen1:gen2
ZZZ Gateway gen1:gen2:Web Proxy (abc)
ZZZ Gateway gen1:gen2
Web Proxy (abc):ZZZ Gateway gen1:gen2:XYZ Systems
Web Proxy (abc):ZZZ Gateway gen1:gen2:XYZ Systems:POP SetUp
Web Proxy (abc):XYZ Systems:POP SetUp:ZZZ Gateway gen1:gen2

What I need to do is find records that have the string "ZZZ Gateway gen1:gen2" - just these that exist within records and replace just this ":" with a ", " ONLY

End result would then be:

Web Proxy (abc):ZZZ Gateway gen1, gen2
ZZZ Gateway gen1, gen2:Web Proxy (abc)
ZZZ Gateway gen1, gen2
Web Proxy (abc):ZZZ Gateway gen1, gen2:XYZ Systems
Web Proxy (abc):ZZZ Gateway gen1, gen2:XYZ Systems:POP SetUp
Web Proxy (abc):XYZ Systems:POP SetUp:ZZZ Gateway gen1, gen2

I have tried it using updates with substr and replace but to no avail.

Answer 1

Looks like you could get away with replacing "1:" with "1,"

update IPDATA 
set info=replace(info, '1:', '1,' )  
where info like('%ZZZ Gateway gen1:gen2%')