Larry:
That works! Thanks.
But I will have up to 2000 data need to pick up. I
won't hard code to 2000 fields. Is there a better way
to loop this or to put in an array?
Thanks a lot.
Steven
- Larry Elkins <elkinsl_at_flash.net> wrote:
> Steven,
>
> For a "pure" SQL solution, you can use INSTR to get
> the n'th occurrence of
> an item. So, you know item 2 is surrounded by the
> 1st and 2nd occurence,
> item 3 by the 2nd and 3rd occurence, etc. The SQL
> looks a little nasty but I
> took it down through item 9 for you to see:
>
> 1 select substr(foo,1,instr(foo,'^',1,1)-1) f1,
> 2 substr(foo,instr(foo,'^',1,1)+1, -- find
> the first ^ and add 1
> 3
> instr(foo,'^',1,2)-instr(foo,'^',1,1)-1) f2, -- find
> the second occurence minus the first occurence
> 4 substr(foo,instr(foo,'^',1,2)+1,
> 5
> instr(foo,'^',1,3)-instr(foo,'^',1,2)-1) f3,
> 6 substr(foo,instr(foo,'^',1,3)+1,
> 7
> instr(foo,'^',1,4)-instr(foo,'^',1,3)-1) f4,
> 8 substr(foo,instr(foo,'^',1,4)+1,
> 9
> instr(foo,'^',1,5)-instr(foo,'^',1,4)-1) f5,
> 10 substr(foo,instr(foo,'^',1,5)+1,
> 11
> instr(foo,'^',1,6)-instr(foo,'^',1,5)-1) f6,
> 12 substr(foo,instr(foo,'^',1,6)+1,
> 13
> instr(foo,'^',1,7)-instr(foo,'^',1,6)-1) f7,
> 14 substr(foo,instr(foo,'^',1,7)+1,
> 15
> instr(foo,'^',1,8)-instr(foo,'^',1,7)-1) f8,
> 16 substr(foo,instr(foo,'^',1,8)+1,
> 17
> instr(foo,'^',1,9)-instr(foo,'^',1,8)-1) f9
> 18* from steven
> SQL> /
>
> F1 F2 F3 F4
> F5
> --------------- --------------- ---------------
> --------------- ------------
> ---
> F6 F7 F8 F9
> --------------- --------------- ---------------
> ---------------
> CIRCUITS00001 12/31/01
>
> CIRCUITS
>
> DESKTOP-000001 333
>
> DESKTOP
>
>
> Larry G. Elkins
> elkinsl_at_flash.net
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On
> Behalf Of
> > steven wndy
> > Sent: Tuesday, December 11, 2001 5:50 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Instr function ?
> >
> >
> > Hello:
> >
> > My table has a column (EVFIELDS) contain the
> > concatenated information pass in (up to 20 fileds
> from
> > other tables which will be used to update into our
> > database), each piece of information will be
> separated
> > by ^ in this column and some info are nullable,
> then
> > the data for this column would be like:
> > 1/
> >
>
CIRCUITS00001^^12/31/01^^^^^^CIRCUITS^^^^^^^^^^^^^^^^^^^^^^^^^
> > ^^^^^^^^^^^^^^
> >
> > 2/
> > DESKTOP-000001^333^^^^^^^DESKTOP
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >
> > The first example above I have data in pos1, pos3,
> > pos9, and others are NULL;
> > The second example there are data in pos1,
> pos2,pos9,
> > and others are NULL;
> >
> > I tried INSTR. But how do I find the start and end
> > points for the postion like pos3 or pos11?
> > In another word, how can I filter/select those
> data
> > out of these ^? Should I loop for the postion 1
> to
> > 2000? Any idea?
> >
> > Desc c_eventout:
> > EVTYPE VARCHAR2(64)
> > EVTIME DATE
> > EVUSER VARCHAR2(64)
> > EVFIELDS VARCHAR2(2000)
> > EVSEPCHAR VARCHAR2(1)
> >
> > Thanks for your reply
> >
> >
> > Steven
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Larry Elkins
> INET: elkinsl_at_flash.net
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at
http://shopping.yahoo.com
or bid at
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: steven wndy
INET: wndyu_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Dec 12 2001 - 09:53:31 CST