Re: substr instr

From: Adam Musch <ahmusch_at_gmail.com>
Date: Tue, 28 Jul 2009 14:01:31 -0500
Message-ID: <516d05a0907281201g3bcff222o43f8b0f43838529c_at_mail.gmail.com>

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_at_gmail.com> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 28 2009 - 14:01:31 CDT