RE: Instr function ?

From: steven wndy <wndyu_at_yahoo.com>
Date: Wed, 12 Dec 2001 15:25:20 -0800
Message-ID: <F001.003DB573.20011212145521@fatcity.com>

I have this work in a SP now:

IF sep =1 THEN

select substr(EVFIELDS,1,instr(EVFIELDS,'^',1,1)-1) into update_Text

        from c_eventout where EVTYPE ='abc'; ELSE
        select substr(EVFIELDS,
instr(EVFIELDS,'^',1,sep-1)+1,

instr(EVFIELDS,'^',1,sep)-instr(EVFIELDS,'^',1,sep-1)-1)

                        
        into update_Text  
        from c_eventout  where EVTYPE ='abc';

END IF; Thanks for all the help

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 - 17:25:20 CST