RE: substr instr
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <barb.baker@xxxxxxxxx>
- Date: Tue, 28 Jul 2009 14:47:53 -0400
Barb,
Here you go. Remember - INSTR just gives you the position of the string
you are searching for while SUBSTR works on math. So your second param
to SUBSTR has to use the first INSTR result to correctly calculate how
long of a chunk to take.
Tom
select
SUBSTR('Start net counter : 26798, 24794',
INSTR('Start net counter : 26798, 24794', ':', 1, 1)+1,
INSTR('Start net counter : 26798, 24794', ',', 1,1)-
INSTR('Start net counter : 26798, 24794', ':', 1, 1)-1) from
dual;
Tom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker
Sent: Tuesday, July 28, 2009 2:37 PM
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
