Home » SQL & PL/SQL » SQL & PL/SQL » Building the string having n no of '&' occurrences. (10g without varrays)
Building the string having n no of '&' occurrences. [message #394214] Thu, 26 March 2009 03:17 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
Gurus,

I need to rebuild a string value programmatically which has '&' and insert the same into another table.
Now the string may contain n no of '&' occurrances.
Hence,can you suggest/modify the existing code
so that it can be used in a general way.
Assume creating arrays permission is not granted to the schema.

Creation script is given below :

Create table abc (col1 number,col2 varchar2(100));
Insert into abc values (1,'filename=2GB_Final_Test.zip'||chr(38)||'filename2=280648119'||chr(38)||'filename3=null');
Insert into abc values (2,'filename=c10k-k4u2p10-mz.120-30.S3.bin'||chr(38)||'filename2=268438016'||chr(38)||'filename3=null');
Insert into abc values (3,'filename=oracle.exe'||chr(38)||'filename2=12345'||chr(38)||' filename3=23456');
Insert into abc values (4,'filename=c12kprp-boot-mz.120-33.S1.bin'||chr(38)||'filename2=278725201'||chr(38)||'filename3=null');
Insert into abc values (5,'filename=oracle.exe'||chr(38)||'filename2=123455'||chr(38)||'filename3=23456'||chr(38)||'filename4=test');

My code :

SQL> declare
2 lv_count number;
3 i number:=1;
4 lv_len_str number;
5 lv_great number:=0;
6 lv_set_first varchar(1000):=null;
7 lv_set_curr varchar2(1000):=null;
8 lv_prev varchar2(1000):=null;
9 lv_set varchar2(1000):=null;
10 begin
11 select length(col2) into lv_len_str from abc where col1=5;
12 for a in 1..lv_len_str
13 loop
14 select instr(col2,'&',1,i) into lv_count from abc where col1=5;
15 exit when nvl(lv_count,0)=0;
16 i:=i+1;
17 end loop;
18 lv_great:=(i-1);
19 --dbms_output.put_line ('Highest occurrance : '||lv_great);
20 i:=1;
21 select substr(col2,1,instr(col2,'&',1,1)-1) into lv_set from abc where col1=5;
22 dbms_output.put_line ('First Set : '||lv_set);
23 lv_set_first:=lv_set||chr(38);
24 i:=1;
25 for a in 1..lv_great
26 loop
27 dbms_output.put_line ('Value of i here : '||i);
28 select substr(col2,instr(col2,'&',1,i)+1,(instr(col2,'&',1,i+1)-instr(col2,'&',1,i)-1)) into lv_set_curr from abc where col1=5;
29 -- dbms_output.put_line (lv_set);
30 i:=i+1;
31 lv_prev:=lv_set_first;
32 lv_set:=lv_prev||chr(38)||lv_set_curr;
33 dbms_output.put_line (lv_set);
34 exit when i>lv_great;
35 end loop;
36 --dbms_output.put_line ('Overall set : '||lv_set);
37 end;
38 /
First Set : filename=oracle.exe
Value of i here : 1
filename=oracle.exe&&filename2=123455
Value of i here : 2
filename=oracle.exe&&filename3=23456
Value of i here : 3
filename=oracle.exe&&

PL/SQL procedure successfully completed.

Now I want the output to be :
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test which is not happening.

[Updated on: Sun, 05 April 2009 23:47] by Moderator

Report message to a moderator

Re: Building the string having n no of '&' occurrances. [message #394221 is a reply to message #394214] Thu, 26 March 2009 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you describe accurately and concisely how you want to generate the output from the input?

The code is written to only look at the row with Col_1 = 5, and the output you have provided is just the record with col_1=5 from the test data.
Re: Building the string having n no of '&' occurrances. [message #394492 is a reply to message #394221] Fri, 27 March 2009 07:48 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
I want the output to be :
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test
for a given row in a table which is not happening from my given code.

Can you help me in rectifying the same ?
Logic :
Find the total no of '&' occurrances.
Find the sub-strings coming in between '&' and concat them with
chr(38) which will give a '&'.
Re: Building the string having n no of '&' occurrances. [message #394496 is a reply to message #394492] Fri, 27 March 2009 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
J1357 wrote on Fri, 27 March 2009 12:48
I want the output to be :
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test



Well that'd be:
SQL> select col2 from abc where col1 = 5;

COL2
--------------------------------------------------------------------------------
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test


J1357 wrote on Fri, 27 March 2009 12:48

Logic :
Find the total no of '&' occurrances.
Find the sub-strings coming in between '&' and concat them with
chr(38) which will give a '&'.



I read that and assume that you want to replace the amphersands (&) with amphersands (particularly given your sample output) - which would be the same as doing nothing.
But then you wouldn't be trying to write code, so you need to explain more clearly and in more detail what you want to accomplish.
Re: Building the string having n no of '&' occurrances. [message #394590 is a reply to message #394496] Sat, 28 March 2009 01:05 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
Actually, this info os being passed to one procedure which will
insert the reqd details into one table.Hence,
procedure abc (p_input_id number,
p_input_string varchar2) will be 2 input parameters.
The only point where i'm getting stuck is the way to handle multiple '&' in a particular string.
Will you help me rectify the above code which I've started to work on.

Help appreciated
Re: Building the string having n no of '&' occurrances. [message #394819 is a reply to message #394590] Mon, 30 March 2009 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is very difficult to help someone who cannot tell you what it is that they actualy want.

You have said that your process is:
Logic :
 Find the total no of '&' occurrances.
 Find the sub-strings coming in between '&' and concat them with
 chr(38) which will give a '&'.


To do the first, you simple compare length(string) with length(replace(string,'&',''))

The second seems, based on the description and the examples you provided to involve replacing '&' with chr(38), which produces no change to the string.

We can help, but you need to tell us what you want.
Re: Building the string having n no of '&' occurrances. [message #395372 is a reply to message #394819] Wed, 01 April 2009 04:32 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
Goal: I want to rebuild string which has n no of & in it using pl-sql.With my set of given codes and the logic,can some body help me where I'm going wrong ?
Re: Building the string having n no of '&' occurrances. [message #395388 is a reply to message #395372] Wed, 01 April 2009 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We do not understand what you want.

Based on the inputs, outputs and explanations that you have shown us to date, you want to take data from a table like this
Insert into abc values 
(5,'filename=oracle.exe'||chr(38)||'filename2=123455'||chr(38)||'filename3=23456'||chr(38)||'filename4=test');


And return the output
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test


The output data is EXACTLY THE SAME as the input data.

No Pl/Sql process more complex than SELECT ...INTO is required to get this result.

You need to tell us what you are trying to achieve.

What process (in detail) do you want to perform on the input string?

What problem are you trying to solve?

The code you've posted just seems to break the input string down at & markers, and then put it back together with & between the segments - which makes no change to the string.


[Updated on: Thu, 02 April 2009 08:32] by Moderator

Report message to a moderator

Re: Building the string having n no of '&' occurrances. [message #395731 is a reply to message #395388] Thu, 02 April 2009 08:28 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
I've posted the requirement above and that this "oracle.exe&filename2=123455&filename3=23456&filename4=test" is passed through application which i have to programtically insert into database.
Help appreciated.
Re: Building the string having n no of '&' occurrances. [message #395740 is a reply to message #395731] Thu, 02 April 2009 08:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok - it's very simple.
Spend some time actually addressing the questions I've raised, and explaining in what way your output from this procedure is different from the input to it, and I'll help you.

Keep posting exactly the same information, and you'll have to hope that someone else takes an interest in your problem, because I'll be elsewhere, doing other things.



Re: Building the string having n no of '&' occurrances. [message #395742 is a reply to message #394214] Thu, 02 April 2009 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> create table test1 (a varchar2(500));

Table created.

SQL> create table test2 (a varchar2(500));

Table created.

SQL> set define off
SQL> insert into test1 (a) values ('oracle.exe&filename2=123455&filename3=23456&filename4=test');

1 row created.

SQL> insert into test2 select * from test1;

1 row created.

SQL> select a from test1;

A
--------------------------------------------------------------------------------
oracle.exe&filename2=123455&filename3=23456&filename4=test

SQL> select a from test2;

A
--------------------------------------------------------------------------------
oracle.exe&filename2=123455&filename3=23456&filename4=test

SQL> 


Job done.

Maybe if you told us why you think you need to use any PL/SQL at all for this we could tell you what you need to do.
Re: Building the string having n no of '&' occurrances. [message #395835 is a reply to message #395731] Thu, 02 April 2009 15:37 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
J1357 wrote on Thu, 02 April 2009 09:28
I've posted the requirement above and that this "oracle.exe&filename2=123455&filename3=23456&filename4=test" is passed through application which i have to programtically insert into database.
Help appreciated.


You'd make a good politician.
Previous Topic: fractured blocks indetification
Next Topic: Group by on date
Goto Forum:
  


Current Time: Fri Dec 09 03:43:56 CST 2016

Total time taken to generate the page: 0.07581 seconds