| Parse String [message #8068] |
Thu, 24 July 2003 11:26  |
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 #8071 is a reply to message #8068] |
Thu, 24 July 2003 12:08  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
SQL> select '123,54873,6984,32456709' from dual;
'123,54873,6984,3245670
-----------------------
123,54873,6984,32456709
SQL> select replace ('123,54873,6984,32456709',',',' ') from dual;
REPLACE('123,54873,6984
-----------------------
123 54873 6984 32456709
|
|
|
|
| Re: Parse String [message #8072 is a reply to message #8071] |
Thu, 24 July 2003 12:21 |
George B
Messages: 28
Registered: November 2002
|
Junior Member |
|
|
Sorry, but I was not making myself clear.
The 123,54873,6984,32456709 is entered in a field and is accepted as a parameter :P_id. Then I want to take these entries and these will be the arguments in a where clause: where id IN (123 54873 6984 32456709).
Not to familiar with substr and instr but can this be used in this situation or is there a simpler approach
thank you.
|
|
|
|
| Re: Parse String [message #8074 is a reply to message #8072] |
Thu, 24 July 2003 13:33 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Maybe I still am not getting it, but from what you explained I don't see why not use REPLACE:
SQL> INSERT INTO t values ('123 54873 6984 32456709');
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p(p_id IN VARCHAR2)
2 AS
3 strId VARCHAR2(30);
4 BEGIN
5
6 SELECT id INTO strid FROM t WHERE id=REPLACE(p_id,',',' ');
7 DBMS_OUTPUT.PUT_LINE(strId);
8 END;
9 /
Procedure created.
SQL> EXEC p('123,54873,6984,32456709');
123 54873 6984 32456709
|
|
|
|
| Re: Parse String [message #8075 is a reply to message #8074] |
Thu, 24 July 2003 14:40 |
George B
Messages: 28
Registered: November 2002
|
Junior Member |
|
|
the parameter will accept either one or more entries, no limit as to the number but always delimited by a comma and data type is char(100). the column in the table is numeric datatype. For each id it will retrieve data and print out a form letter with the specific id.
I hope this is much clearer. sorry for not being specific. I am new to Oracle...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
