• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Oracle 9i - Select part of a variable length string

Experts,
I am trying to select a value from the middle of a variable length string.  I think SUBSTR with INSTR will work but am having trouble with the syntax

in this example string:
'10058698.325 Fatal error reported: No Item <GMAFH - 60-90161> was found. File name /u02/......'

I want to return everything after 'reported:' and before 'File' so I return:
No Item <GMAFH - 60-90161> was found

I have figured out how to use INSTR to select the up to or after the words, but can figure out how two combine both.
0
JDCam
Asked:
JDCam
  • 2
1 Solution
 
sdstuberCommented:
SELECT SUBSTR(your_column, b, e - b)
  FROM (SELECT your_column, INSTR(your_column, 'reported:') + 9 b, INSTR(your_column, 'File') e
          FROM your_table)
0
 
sdstuberCommented:
or directly...

SELECT SUBSTR(
           your_column,
           INSTR(your_column, 'reported:') + 9,
           INSTR(your_column, 'File') - (INSTR(your_column, 'reported:') + 9)
       )
  FROM your_table;

and, if the space after "reported: " shouldn't be included in the output, adjust the offsets


SELECT SUBSTR(
           your_column,
           INSTR(your_column, 'reported: ') + 10,
           INSTR(your_column, 'File') - (INSTR(your_column, 'reported: ') + 10)
       )
  FROM your_table;
0
 
JDCamAuthor Commented:
thanks... working great
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now