Home » SQL & PL/SQL » SQL & PL/SQL » comma seperated input string to number
comma seperated input string to number [message #12889] Wed, 09 June 2004 21:40 Go to next message
Srinath
Messages: 40
Registered: May 2002
Member
Hi All:

I am getting a input variable as comma seperated string in to a procedure. eg:

x varchar2 say x as '1,2,3'

i need to put this as number in a select statement variable 

select x from abc where number in (1,2,3)

the variable should hold 1,2,3 which need to pass as 1,2,3

Please post your ideas.  I do not want them seperately as 1 2 3

i need to use them as a variable as a whole

 

thank you.

Srinath
Re: comma seperated input string to number [message #12891 is a reply to message #12889] Wed, 09 June 2004 22:25 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Convert the string into an SQL-friendly collection (you need to write your own "split" function(s) as PL/SQL doesn't have one) - or better still, pass a collection into the procedure in the first place.

Then the query becomes something like:
SELECT x
FROM   abc
WHERE  number IN
       ( SELECT column_value
         FROM   TABLE(collectionvar) );
Re: comma seperated input string to number [message #222912 is a reply to message #12889] Tue, 06 March 2007 23:25 Go to previous messageGo to next message
jilhewar_santosh
Messages: 13
Registered: July 2006
Location: mumbai
Junior Member
Hi,
Try below query
SELECT translate('1,2,3,,', '$,','@')
from dual
Re: comma seperated input string to number [message #222976 is a reply to message #222912] Wed, 07 March 2007 02:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is an easier way of achieving the same effect:
SELECT replace('1,2,3,,', ',','')
from dual; 
, but why have you posted this? It has nothing at all to do with the question that was asked.
Re: comma seperated input string to number [message #222978 is a reply to message #222976] Wed, 07 March 2007 02:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... and the thread is three years old...
Re: comma seperated input string to number [message #222990 is a reply to message #222978] Wed, 07 March 2007 02:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh. Embarassed
I missed that.
Previous Topic: Analytic Functions - LAG
Next Topic: Read content from file in Unix
Goto Forum:
  


Current Time: Mon Dec 05 15:03:40 CST 2016

Total time taken to generate the page: 0.06128 seconds