| Parse String [message #8070] |
Thu, 24 July 2003 11:27  |
George B
Messages: 28
Registered: November 2002
|
Junior Member |
|
|
I have a string '123,54873,6984,32456709' and I want to parse and remove the commas to have the result as
123 54873 6984 32456709. The result will be fed into this SQL statement
where
idn in (123 54873 6984 32456709)
Thank you for your help.
|
|
|
|
| Re: Parse String [message #8077 is a reply to message #8070] |
Thu, 24 July 2003 23:42  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
1. You need the comma's in the list for your IN clause.
2. You should use dynamic sql. You cannot provide this in a plain SQL statement. Look at the following anonymous block, just to give an example:SQL> Declare
2 Type curtype is ref cursor;
3 c_cur curtype;
4 v_list varchar2(250) := '&test';
5 v_result Number;
6 Begin
7 Open c_cur For 'Select 1 From Dual Where 25 in ('||nvl(v_list,'3')||')';
8 Loop
9 Fetch c_cur Into v_result;
10 Exit When c_cur%NOTFOUND;
11 dbms_output.put_line('do some processing');
12 End Loop;
13 End;
14 /
Enter value for test: 1,2,3,4,25
old 4: v_list varchar2(250) := '&test';
new 4: v_list varchar2(250) := '1,2,3,4,25';
do some processing
PL/SQL procedure successfully completed.
SQL>
HTH,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
