RE: substr instr

  • From: Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>
  • To: "'barb.baker@xxxxxxxxx'" <barb.baker@xxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 28 Jul 2009 11:43:13 -0700

Try this:

select trim(substr(eventtext ,instr(eventtext ,':')+1))
from pecom_event where eventtext like '%Start net%';

Regards,

Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
  Please consider the environment before printing this e-mail.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Barbara Baker
Sent: Tuesday, July 28, 2009 11:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: substr instr

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


��i��0���zX���+��n��{�+i�^

Other related posts: