String manipulations

From: nirav <shivam71_at_gmail.com>
Date: 27 Mar 2007 10:37:35 -0700
Message-ID: <1175017055.361915.129110@o5g2000hsb.googlegroups.com>

I have a need to modify data in a table....the data is varchar ...here are its details:
SQL> desc ns

 Name                                                  Null?    Type
 ----------------------------------------------------- --------


------------------------------------

 ACTY
VARCHAR2(800) SQL> select * from ns;

ACTY

---

Mohan (x,y,z) went to see Joe
Mohan(xxxx,yy,z) was worried, Joel called and Mohan (x,yyy,zz) responded to her
Mohan(x,y,zzzz) was glad, party was all set, Mohan (xx,yy,z) called all his friends but Mohan(x,y,z) did not call Mona

How should it be modifed? - I have to add the string '~,' after the second comma after the opening bracket that follows the word Mohan.

so expected result is:
Mohan (x,y,~,z) went to see Joe
Mohan(xxxx,yy,~,z) was worried, Joel called and Mohan (x,yyy,~,zz) responded to her
Mohan(x,y,~,zzzz) was glad, party was all set, Mohan (xx,yy,~,z) called all his friends but Mohan(x,y,~,z) did not call Mona

Can this be done via sql? I know pl/sql will solve it , and its not too tough to work out that way, but can a purely sql based solution meet this need?

Thanks!
Nirav Received on Tue Mar 27 2007 - 12:37:35 CDT