Home » SQL & PL/SQL » SQL & PL/SQL » Split a string
Split a string [message #396216] Sun, 05 April 2009 02:15 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
Gurus,
Sorry to missed on this info as parallely working on similar type of code.
I'm having one procedure where user inputs 2 values :
id which is 11111,22222,33333,etc
and string which is combination of parsed strings parsed with character '#'.
Now if user passes 1 and string which is
filename=oracle.exe&mdfid=123455&relmdfid=44545454#filename=java.exe&mdfid=4343434&relmdfid=656565, these would get inserted in a table.
Look of table would be :
id file_name
----------------------------------------------------------------------------------------------
11111 filename=oracle.exe&mdfid=123455&relmdfid=44545454
11111 filename=java.exe&mdfid=4343434&relmdfid=656565

Can you provide me with working code for the same?
Help appreciated ..Hope everything has been cleared from my side this time.
Re: Building the string having n no of '&' occurrances. [message #396219 is a reply to message #396216] Sun, 05 April 2009 02:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I couldn't see any relation between the subject of OP and this requirement.

Basically you want to split the string whenever you hit a #.

Try to use instr ans substr in a loop.

By
Vamsi

[Updated on: Sun, 05 April 2009 02:29]

Report message to a moderator

Re: Building the string having n no of '&' occurrances. [message #396238 is a reply to message #396219] Sun, 05 April 2009 08:07 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
Vamsi,
I know to use instr and substr in loop.
But the problem is that of while inserting '&' in oracle.
That's why i need your help.
Thanks in adv
Re: Building the string having n no of '&' occurrances. [message #396241 is a reply to message #396238] Sun, 05 April 2009 08:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is the issue in it?
SQL> create table vam_tab (n number,x varchar2(100));

Table created.
SQL> create or replace procedure vam_proc(pn number,px varchar2) is
  2  begin
  3  insert into vam_tab values(pn,px);
  4  end;
  5  /

Procedure created.

SQL> exec vam_proc(&n,'&x');
Enter value for n: 1
Enter value for x: vam&tab&proc

PL/SQL procedure successfully completed.

SQL> exec vam_proc(&n,'&x');
Enter value for n: 2
Enter value for x: &&&&&123&45&vamsdfasdlf&asdf&

PL/SQL procedure successfully completed.

SQL> select * from vam_tab;
         N X
---------- ------------------------------
         1 vam&tab&proc
         2 &&&&&123&45&vamsdfasdlf&asdf&
Next time provide the steps like above, whatever you have tried.

By
Vamsi
Re: Building the string having n no of '&' occurrances. [message #396421 is a reply to message #396241] Mon, 06 April 2009 09:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Got PM from J1357
Quote:
First of all thanks for your quick response.
You must have set define to off in the sql prompt and then inserted those values.
My problem lies when pl comes into scenario as the values
"filename=oracle.exe&filename2=1233445&filename3=23456667" is being passed through application.
Now, our application is having max 3 occurrances of '&' in the entire string above.
But i need to generalise the said code which would work for any no of '&' in the string.
Hope, I made my requirement clear.
By
Vamsi
Re: Split a string [message #396427 is a reply to message #396216] Mon, 06 April 2009 10:54 Go to previous message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
And that brings us straight back to the original thread:
http://www.orafaq.com/forum/m/394214/129190/#msg_394214

J1357:

We still don't know why you think you need to write any code to handle ampersands (&).

Although I'm beginning to think the problem is you don't really understand what define does.

PL/SQL does not treat amphersands any different to any other character.
SQLplus does treat them differently - it treats them as substitution variables and you need to use set define off to stop it doing that.

However - Vamsi's example above did not use set define off and it didn't need to. sqlplus does not check the value supplied for a substitution variable for more substitution variables.

Previous Topic: Query to become a new table
Next Topic: Tricky query
Goto Forum:
  


Current Time: Tue Feb 11 06:21:28 CST 2025