| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Instr function ?
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) f918* from steven
---
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).
Received on Wed Dec 12 2001 - 00:29:35 CST
 |
 |