Re: substr instr
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: barb.baker@xxxxxxxxx
- Date: Tue, 28 Jul 2009 14:01:31 -0500
SQL> variable test varchar2(200);
SQL> exec :test := 'Start net counter : 182552, 179058';
PL/SQL procedure successfully completed.
SQL> l
1 select trim(
2 substr(:test,
3 instr(:test, ':') + 1, -- position after
leading colon
4 instr(:test, ',') -- position of leading comma
5 - instr(:test, ':') - 1) -- less position of
leading colon to get string length
6 )
7* from dual
SQL> /
TRIM(SUBSTR(:TEST,INSTR(:TEST,':')+1,--POSITIONAFTERLEADINGCOLONINSTR(:TEST,',')--POSITIONOFLEADINGC
----------------------------------------------------------------------------------------------------
182552
SQL>
On Tue, Jul 28, 2009 at 1:36 PM, Barbara Baker<barb.baker@xxxxxxxxx> wrote:
> Does anyone want to take pity on me and help me with this substr/instr
> problem?
> I've wasted hours trying to come up with the "magic combination"
>
> I have thousands of records that look like this:
>
> select eventtext from pecom_event where eventtext like '%Start net%';
> --------------------------------------------------------------------------------
> Start net counter : 182552, 179058
> Start net counter : 201354, 197592
> Start net counter : 203542, 201282
> Start net counter : 205420, 201284
> Start net counter : 7732, 6854
> Start net counter : 9164, 6870
> Start net counter : 26798, 24792
> Start net counter : 26798, 24794
>
> I need the number after the : but before the comma, for example, 26798
> in the last record
>
> I've gotten as far as getting anything past the : then I get stuck:
>
> select
> SUBSTR('Start net counter : 26798, 24794',
> INSTR('Start net counter : 26798, 24794', ':', 1, 1)+1,
> INSTR('Start net counter : 26798, 24794', ',', 1,1)-1)
> from dual;
>
> SUBSTR('START
> -------------
> 26798, 24794
>
> I'd be grateful for any help. Data is currently in a 9.2.0.7 oracle
> database on linux.
>
> Thanks!
> Barb Baker
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
