Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

parsing a string 1

Status
Not open for further replies.

MickTheBelgian

Programmer
Jan 11, 2001
160
DK
I will get a string from a message queue, having +/- the format Name1=Value1;Name2=Value2;.... I will need to do a "UPDATE XXX SET Name1=Value1 WHERE Name2=Value2;" I am still talking to the guys who make the string and can ask for a different format if I want. Any suggestions on a strategy for this? Is there an easy way to split up a string at every ";", or should I ask for padding to equal length?
 
Hi,
If you have an Update statment already written as follows:

Update Table
Set name=L_name1
where Name2=L_name2;

then make use of following code:

Assumption: There is only name1 & name2 & there values present in the string.

Code:
SQL> Declare
     L_str Varchar2(1000):='name1=abc;name2=xyz;';
     L_name1 Varchar2(1000):=null;
     L_name2 Varchar2(1000):=null;
     L_str_Len Number(10):=Length(L_str);
     L_ctr1 Number(10):=0;
     L_ctr2 Number(10):=0;
   Begin
     L_ctr1:=instr(L_str,';');
     L_ctr2:=instr(L_str,'=');
     L_name1:=substr(L_str,(L_ctr2+1),((L_ctr1-L_ctr2)-1)) ;
     dbms_output.put_line(l_name1);
     L_str:=substr(L_str,(L_ctr1+1));
     L_ctr1:=instr(L_str,';');
     L_ctr2:=instr(L_str,'=');
     L_name2:=substr(L_str,(L_ctr2+1),((L_ctr1-L_ctr2)-1)) ;
     dbms_output.put_line(L_name2);
   end;
  /

abc
xyz

PL/SQL procedure successfully completed.

If you have nore values then you may use the same code in loop by claculating the String length.

If you do not have the update statemet ready as described above then you will have to make use of Dynamic SQL.

HTH
regards
Himanshu
 
I would like the MQ to trigger different Stored Procs. I never done dynamic SQL but I looked it up in the manual and it looks good. Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top