T.J,
There's a few ways to do this, here's one:
SQL> select ',1, 2, 3, 4, 5, 6,' as v_name from dual;
V_NAME
------------------
,1, 2, 3, 4, 5, 6,
SQL> select to_number(
2 ltrim(
3 substr(x.v_name,
4 instr(x.v_name,',',1,iter.pos),
5
instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
v_name
6 from (select ',1, 2, 3, 4, 5, 6,' as v_name from dual) x,
7 (select rownum pos from emp) iter
8 where iter.pos <=
length(x.v_name)-length(replace(x.v_name,','))-1;
V_NAME
----------
1
2
3
4
5
6
SQL> select to_number(
2 ltrim(
3 substr(x.v_name,
4 instr(x.v_name,',',1,iter.pos),
5
instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
v_name
6 from (select ',1, 2,' as v_name from dual) x,
7 (select rownum pos from emp) iter
8 where iter.pos <= length(x.v_name)-length(replace(x.v_name,','))-1;
V_NAME
----------
1
2
SQL> select to_number(
2 ltrim(
3 substr(x.v_name,
4 instr(x.v_name,',',1,iter.pos),
5
instr(x.v_name,',',1,iter.pos+1)-instr(x.v_name,',',1,iter.pos)),','))
v_name
6 from (select ',2,4,6,' as v_name from dual) x,
7 (select rownum pos from emp) iter
8 where iter.pos <= length(x.v_name)-length(replace(x.v_name,','))-1;
V_NAME
----------
2
4
6
the idea is to use a pivot table (a cartesian) to generate n rows
(n = the numbers of values in the string), then use substr and instr
to parse them out.
Once you get the values into rows, you can bulk collect into an array
or use a for loop.
Ofcourse, you could always just use a for loop and substr through each
char of the string.
Hope that helps,
Anthony
-----Original Message-----
From: Randall TJ
[mailto:oracledba-ezmlmshield-x78803132.[Email address protected]
Sent: Wednesday, March 02, 2005 9:42 AM
To: LazyDBA Discussion
Subject: Parsing a comma-delimited string
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