You could also use

dbms_utility.comma_to_table (see oracle docs for info)

That takes all the hard work out of it but note that the items within the
delimited string need to be encapsulated by double quotes
eg. '"1","2","3"'


"Randall TJ " <oracledba-ezmlmshield-x78803132.[Email address protected]
wrote on 02/03/2005 14:42:06:

> I have an alarm string that is sometimes comma delimited - some
> examples are: '1', '1, 2', '1, 2, 3, 4, 5, 6' (There could be one
> alarm, and up to six alarms, spaced as shown in the example).
>
> I can hard code the substr code like:
>
> v_name := '1, 2, 3, 4, 5, 6';
>
> SELECT substr( v_name,1, 1 ) alarm1,
> substr( v_name,4, 1 ) alarm2,
> substr( v_name,7, 1 ) alarm3,
> substr( v_name,10, 1 ) alarm4,
> substr( v_name,13, 1 ) alarm5,
> substr( v_name,16, 1 ) alarm6
> INTO v_First, v_Second, v_Third,v_Fourth,v_Fifth,v_Sixth
> from dual;
>
> But my spidey senses are on high, since the engineers swear that the
> alarms cannot be transmitted in any other way. I would like to find
> a way to parse the alarms based on comma position.
>
> Any help would be greatly appreciated!
>
> Thank you.
>
> T.j.
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>

Oracle LazyDBA home page