Home » SQL & PL/SQL » SQL & PL/SQL » String manipulations
String manipulations [message #227202] Tue, 27 March 2007 09:12 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

I have a table, where a string 'Mohan' needs to be updated with another string 'Mohan Patel'...the issue is that in each row the word Mohan may occur from ZERO to MANY times and all such ocurrances need to change...the table & sample data is as under...

SQL> desc ns
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ACTY                                                           VARCHAR2(800)

SQL> select * from ns;

ACTY
----------------------------------------------------------------------------------------------------
Mohan went to see Joe
Mohan was worried, Joel called and Mohan responded to her
Mohan was glad, party was all set, Mohan called his friends, but Mohan did not call Mona


so the data should get replaced like:
Mohan Patel went to see Joe
Mohan Patel was worried, Joel called and Mohan Patel responded to her

I know that by pl/sql this can be a trivial task..but can this be managed in SQL? Thanks a lot for ur help...in an hour or so I will post the query which takes me a little near the solution..but just a little near...

Thank you,
Nirav



Re: String manipulations [message #227208 is a reply to message #227202] Tue, 27 March 2007 09:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
One word (REPLACE).
Re: String manipulations [message #227224 is a reply to message #227208] Tue, 27 March 2007 10:14 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Ok, I was not clear in specifying the requirements correctly...sorry but here are the corrected requirements..
The word Mohan ...actually name of a procedure has 3 parameters...now I need to add a string '~,' after the second comma in the bracket...


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

now requirement is that after the change the data should be like:
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

Basically in the bracket after Mohan, after the second comma, the string '~,' needs to be added...how many times the String Mohan occurs is variable...inside the brackets it takes 3 varchar parameters (x,y,z) etc...and the data of these parameters is variable..like in one row it is: Mohan(xxxx,yy,z) and in the same row, there is another instance where it is:
Mohan(x,yyy,zz)...Can it still be done via sql?

[Updated on: Tue, 27 March 2007 12:26]

Report message to a moderator

Re: String manipulations [message #227231 is a reply to message #227224] Tue, 27 March 2007 10:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Changing horses on me.

However, based on your NEW sample data above, my answer is still the same (REPLACE).
Re: String manipulations [message #227232 is a reply to message #227224] Tue, 27 March 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can this be done in SQL?
Yes.
Is this more efficient than a PL/SQL proc?
No as soon as there are sufficient rows.

Regards
Michel
Re: String manipulations [message #227243 is a reply to message #227232] Tue, 27 March 2007 12:24 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
No, how can you make use of REPLACE function here?Replace requires a well defined string and then acts on it..but here the format of the string is not fixed, it keeps changing...Please see this data:

Mohan (x,y,z)
Mohan(xxxx,yy,z) etc...remember a '~,' is required to be added after second comma...but the second comma does not occur at a fixed position!

if still u feel I am wrong, can you take the table's data and show that a REPLACE will work to meet the need i specified?

Michel: thanks, but I am , currently trying to check if sql can meet this need, and not considering pl/sql. may be later i will check pl/sql part and compare performance etc..for now I only need a working solution in SQL.

Re: String manipulations [message #227245 is a reply to message #227243] Tue, 27 March 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can be done.
This is the only thing I can say you.
I already posted some answers for this kind of questions on AskTom.
Now, it is not an easy one and it takes long for me to build such a query to post it here.
Search on AskTom, try to create it and come back with a first version, I'll be able to correct it.

Regards
Michel
Re: String manipulations [message #227247 is a reply to message #227245] Tue, 27 March 2007 12:46 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
That's fine,thanks! I will post what I can make out with sql-I said in an hour, but was pulled away on other tasks- but will certainly post tomorrow...

[Updated on: Tue, 27 March 2007 12:47]

Report message to a moderator

Re: String manipulations [message #227253 is a reply to message #227247] Tue, 27 March 2007 13:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Don't over analyze the problem. Consider the pattern you want to replace.

If you examples still hold, you bascially want to replace y,z with y,~,z . Now use the REPLACE function with this knowledge and see what you get.
Re: String manipulations [message #227256 is a reply to message #227253] Tue, 27 March 2007 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think "y,z" is just an exemple of strings that can happen.
If I clearly understand the OP, he wants to replace (to keep the replace word) the second "," between "(" and ")" to ",~" whatever can be before and after this second ",".

Regards
Michel
Re: String manipulations [message #227258 is a reply to message #227253] Tue, 27 March 2007 13:37 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Quote:

you bascially want to replace y,z with y,~,z


No!!! the second argument could be ANyTHING!! LIKE IT COULD BE m, p, t, y , z, or ANYTHING!!! what I gave here is just a sample.
Ok, the fourth row could be like:

Mohan (x,m,z) went to see Joe 
this should change to :Mohan (x,m,~,z) went to see Joe 



[Updated on: Tue, 27 March 2007 13:40]

Report message to a moderator

Re: String manipulations [message #227262 is a reply to message #227258] Tue, 27 March 2007 14:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
OK. That's why I emphasized "if your sample data is accurate", which now it appears it wasn't a good sample set.

Let me guess, you aren't on 10g yet ? If you are on 10g, you can consider using:

SQL> select * from test_rep;

PHRASE
--------------------------------------------------------------
Mohan (x,y,z) talks to Mona
Mohan (xxx,m,z) bet against George and Mohan (x,y,z) lost

SQL> select regexp_replace(phrase, '(\([^,]+,)([^,]+,)([^,]+\))', '\1\2~,\3') new_phrase
  2	from test_rep;

NEW_PHRASE
--------------------------------------------------------------
Mohan (x,y,~,z) talks to Mona
Mohan (xxx,m,~,z) bet against George and Mohan (x,y,~,z) lost
Re: String manipulations [message #227263 is a reply to message #227262] Tue, 27 March 2007 14:07 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
No, I am on Oracle 9i!! Yes today has been a lesson for me! it is just that I have spent more than 4 hours on the actual table with data...causing me to assume things while posting... but its surely my error. well, for 9i any hopes?

[Updated on: Tue, 27 March 2007 14:12]

Report message to a moderator

Re: String manipulations [message #227266 is a reply to message #227263] Tue, 27 March 2007 15:41 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

nirav_hyd wrote on Tue, 27 March 2007 15:07
No, I am on Oracle 9i!! Yes today has been a lesson for me! it is just that I have spent more than 4 hours on the actual table with data...causing me to assume things while posting... but its surely my error. well, for 9i any hopes?



I've been taking a look at this, and so far I've been able to determine how to get the value you need if there is only one reference to Mohan in the column. When that is the case, this is the code you can use:

SQL> select * from ns;

ACTY
--------------------------------------
Mohan (x,y,z) went to see Joe
Mohan(xxxx,yy,z) was worried
Mohan(x,y,zzzz) was glad

SQL> select substr(acty,1,instr(acty, '(') + instr(substr(acty,instr(acty,'('),instr(acty,')')
          - instr(acty,'(') + 1),',',1,2) - 2)||
            replace(substr(acty,instr(acty,'(') + instr(substr(acty,instr(acty,'('),instr(acty,')') 
          - instr(acty,'(') + 1),',',1,2)-1,1),',',',~')||
            substr(acty,instr(acty, '(') + instr(substr(acty,instr(acty,'('),instr(acty,')')
          - instr(acty,'(') + 1),',',1,2) - 1,
            length(acty) - instr(acty, '(') + instr(substr(acty,instr(acty,'('),instr(acty,')') 
          - instr(acty,'(') + 1),',',1,2)) new_acty
    from ns; 
    
NEW_ACTY
----------------------------------------------------------------------------------------------------
Mohan (x,y,~,z) went to see Joe
Mohan(xxxx,yy,~,z) was worried
Mohan(x,y,~,zzzz) was glad

I don't have time to figure out the rest in the cases where 'Mohan' is in the column more than once. But this might at least get you on the right track. You might notice that I didn't even search for 'Mohan', but it determines what is within the parentheses instead and then manipulates the string that way. So, what the code does is it determines the value of the string up to the second comma, then it concatenates that with the second comma (,) being replaced by (,~) then it concatenates the rest of the string on the end. If there were two cases of 'Mohan' being in the column, then you'd have to use the code like I gave, but leave out the part that concatenates the rest of the column to the end. Instead you'd want to concatenate up to the second occurrence of a second comma within the parentheses and then concatenate onto that from there.
Re: String manipulations [message #227295 is a reply to message #227266] Tue, 27 March 2007 22:24 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you ebrian and ericle for all the help...specially in a post that did not make things too clear it was great of you! I will continue to fight on this today as I get to office...if I get anywhere near the solution I will post.somehow I am losing confidence now..because my sql knowledge is not at expert level and I see no complete solution from any stalwarts here!....will have to resort to pl/sql I guess.

Ericle: thanks a lot..one thing just to mention is that, this is an example only of a very complicated mass of thousands of strings...in fact the actual data may have other brackets too...like fifth row could be:

SQL> select * from ns;

ACTY
--------------------------------------
John (t,w,u) and Mohan (x,y,z) went to see Joe


so we must refer to Mohan and then to the second comma..at home I do not have sql etc. so will check ur code against such data in office...but still this is a great pointer. Thanks!

[Updated on: Tue, 27 March 2007 23:34]

Report message to a moderator

Re: String manipulations [message #227325 is a reply to message #227295] Wed, 28 March 2007 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The further in the post I read, the more I think you want to solve a flaw in the design at completely the wrong place.
Why is it you need to add an extra parameter in the middle??
Re: String manipulations [message #227342 is a reply to message #227325] Wed, 28 March 2007 01:28 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Frank,

You are completely true..I am given this task, with NO OPTION to rethink on any design issue. this is already in production, and there is a change in a procedure or keyword(mimicked with the name Mohan etc here). So I have to work this out...I thought this could be probably be done via sql, but not sure now.
Re: String manipulations [message #227348 is a reply to message #227342] Wed, 28 March 2007 01:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is it a packaged procedure? If so, overload it.
Who, in his right mind, adds a parameter to a procedure in the middle of an existing bunch of parameters?
Re: String manipulations [message #227351 is a reply to message #227348] Wed, 28 March 2007 01:39 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
its more like a keyword having a signature...I just have the option of working on this one, do u feel given this complexity, and no option like overloading etc, just a task to work out as required, pl/sql is better than sql for this? I am feeling that way more and more.
Re: String manipulations [message #227369 is a reply to message #227351] Wed, 28 March 2007 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
pl/sql is better than sql for this?

This what I always said.
Here, using SQL is just an exercise.

Regards
Michel
Re: String manipulations [message #227496 is a reply to message #227295] Wed, 28 March 2007 07:13 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

nirav_hyd wrote on Tue, 27 March 2007 23:24
Thank you ebrian and ericle for all the help...specially in a post that did not make things too clear it was great of you! I will continue to fight on this today as I get to office...if I get anywhere near the solution I will post.somehow I am losing confidence now..because my sql knowledge is not at expert level and I see no complete solution from any stalwarts here!....will have to resort to pl/sql I guess.

Ericle: thanks a lot..one thing just to mention is that, this is an example only of a very complicated mass of thousands of strings...in fact the actual data may have other brackets too...like fifth row could be:

SQL> select * from ns;

ACTY
--------------------------------------
John (t,w,u) and Mohan (x,y,z) went to see Joe


so we must refer to Mohan and then to the second comma..at home I do not have sql etc. so will check ur code against such data in office...but still this is a great pointer. Thanks!



My goodness...yeah, I think you're going to have to resort to PL/SQL for this one. This thread should probably be moved over to the PL/SQL Experts forum. Let us know if you figure it out.

Eric

[Updated on: Wed, 28 March 2007 07:15]

Report message to a moderator

Re: String manipulations [message #227578 is a reply to message #227496] Wed, 28 March 2007 12:48 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Quote:

if I get anywhere near the solution I will post


considering that delivery in time to the client is more important than R & D , I saught refuge to pl/sql and lo! within about 4 hours I had a working program that delivered the functional requirements. I wanted to add a check to prevent data corruption , in case the procedure is ran twice..(meaning that it should not update twice...add twice the strings '~,' if the procedure is executed twice -or any number of times for that matter-and was still not done with it when I came home...so tomorrow I will post the humble pl/sql effort I could come up with! What an anticlimax!! I thought that sql will shine as winner , but no, I was wrong!

[Updated on: Wed, 28 March 2007 12:49]

Report message to a moderator

Re: String manipulations [message #227831 is a reply to message #227578] Thu, 29 March 2007 10:59 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
the pl/sql code that worked for me:

SQL> desc ns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACTY_ID                                            VARCHAR2(200)
 ACTY                                               VARCHAR2(500)
(this acty_id is just the primary key...its just kept a few characters of acty...does not impact this solution..)
SQL> select * from ns;

ACTY_ID
--------------------------------------------------------------------------------
ACTY
--------------------------------------------------------------------------------
Mohan (x,y,z) went t
Mohan (x,y,z) went to see Joe

Mohan(xxxx,yy,z) was
Mohan(xxxx,yy,z) was worried, Joel called and Mohan (x,yyy,zz) responded to her

Mohan(x,y,zzzz) was
Mohan(x,y,zzzz) was glad, party was all set, Mohan (xx,yy,z) but Mohan(x,y,z) di
d not call Mona

ACTY_ID
--------------------------------------------------------------------------------
ACTY
--------------------------------------------------------------------------------

d
d

--now the procedure...


col start_time new_value start_time
col end_time new_value end_time
set serveroutput on size 99999

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') start_time from dual;

Create or replace procedure Mohan_proc as

 v_str varchar2(2048);
 v_orig_str varchar2(2048);
 v_rest_str varchar2(2048);
 v_fourth_comma number;
 v_closing_brace number;
 v_start_location number :=  0;
 Cursor c is select acty_id,acty from ns where
 acty like '%Mohan%' 
-- AND ROWNUM<2
 ;

begin

 for p_rec in c loop

 v_str:=p_rec.acty;
 v_start_location := 0;

  loop

    v_start_location := instr(v_str,',',instr(v_str,'Mohan',v_start_location + 1),2);  	

    if v_start_location<>0 then	
      v_str:=substr(v_str,1,v_start_location)||'~,'||substr(v_str,v_start_location+1);
--      dbms_output.put_line('first part '||substr(v_str,1,v_start_location));
--      dbms_output.put_line('other  part '||substr(v_str,v_start_location+1));
    end if;	
      
      v_closing_brace:=instr(v_str,')',v_start_location,1);
      v_rest_str:=substr(v_str,v_start_location+1,v_closing_brace-v_start_location);

      --the if clause performs a check that prevents duplicate updation, even if the script is run twice

      if (instr(v_rest_str,',',1,3)=0)  then	

       update ns set acty=v_str where acty_id=p_rec.acty_id;

      else

        v_str:= v_orig_str;
        exit;

      end if;	

   exit when v_start_location = 0;

  end loop;
		
 end loop;

 commit;

end;    

/

show err

prompt executing the procedure...

exec Mohan_proc


prompt Dropping the procedure after its execution

DROP procedure Mohan_proc


prompt finding the time spent in script execution

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') end_time
from dual;

select 
(to_date('&end_time','mm/dd/yyyy hh24:mi:ss') - to_date('&start_time','mm/dd/yyyy hh24:mi:ss')) *24*60*60 

total_time_in_seconds
from dual;

sho err

--now its execution and the data after execution..

SQL>  @d:\mohan

START_TIME
-------------------
03/29/2007 21:19:01


Procedure created.

No errors.
executing the procedure...

PL/SQL procedure successfully completed.

Dropping the procedure after its execution
finding the time spent in script execution

END_TIME
-------------------
03/29/2007 21:19:01

old   2: (to_date('&end_time','mm/dd/yyyy hh24:mi:ss') - to_date('&start_time','mm/dd/yyyy hh24:mi:s
new   2: (to_date('03/29/2007 21:19:01','mm/dd/yyyy hh24:mi:ss') - to_date('03/29/2007 21:19:01','mm

TOTAL_TIME_IN_SECONDS
---------------------
                    0

No errors.

--the results..
SQL> select * from ns;

ACTY_ID
--------------------------------------------------------------------------------
ACTY
--------------------------------------------------------------------------------
Mohan (x,y,z) went t
Mohan (x,y,~,z) went to see Joe

Mohan(xxxx,yy,z) was
Mohan(xxxx,yy,~,z) was worried, Joel called and Mohan (x,yyy,~,zz) responded to
her

Mohan(x,y,zzzz) was
Mohan(x,y,~,zzzz) was glad, party was all set, Mohan (xx,yy,~,z) but Mohan(x,y,~

ACTY_ID
--------------------------------------------------------------------------------
ACTY
--------------------------------------------------------------------------------
,z) did not call Mona

d
d



this worked out...sql could have delivered this I kept hoping , but I think that was wrong..

Any suggestions to improve this pl/sql are welcome & I shall be thankful for it.
Thanks
Nirav

[Updated on: Thu, 29 March 2007 11:00]

Report message to a moderator

Previous Topic: what is difference
Next Topic: IMPLEMENTING COMMIT IN TRIGGERS
Goto Forum:
  


Current Time: Sat Dec 10 11:13:48 CST 2016

Total time taken to generate the page: 0.06472 seconds