Solved

Oracle 9i - Select part of a variable length string

Posted on 2014-10-16
3
335 Views
Last Modified: 2014-11-11
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
Comment
Question by:JDCam
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40385446
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40385464
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
 

Author Closing Comment

by:JDCam
ID: 40435881
thanks... working great
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
Create Index on a Materialized View 5 32
Filtering characters in an SQL field 2 16
SQL Recursion 6 16
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question