Regular Expression

Posted on 2012-09-13
Last Modified: 2012-09-13
Can you please give me an example for the following task?

I need to extract the number after "thread" from the following string using regexp_substr in Oracle:

Archived Log entry 86041 added for thread 2 sequence 8100 ID 0xffffffff89e2d6df dest 1:

I was able to extract the first and the last number but not the one following the word thread.

I'd be glad for an example.

Thank you
Question by:skahlert2010

    Author Comment

    Okay, I figured out that I may use this

    REGEXP_SUBSTR(mystring, '[[:digit:]]+',2,2)

    Open in new window

    However, there are probably numerous other attempts that are interesting to learn. So please don't hesitate if you have a suggestion!
    LVL 73

    Assisted Solution

    if 11g, you can use subexpressions

    regexp_substr(mystring,'thread ([0-9]+)',1,1,null,1)

    without subexpression

    substr(regexp_substr(mystring,'thread [0-9]+'),8)
    LVL 73

    Expert Comment

    you can use the posix class instead of range specifier in either of the solutions above
    LVL 73

    Accepted Solution

    another option without subexpressions

    REGEXP_SUBSTR(mystring, '[0-9]+', INSTR(mystring, 'thread '))

    and again,  you can use [[:digit:]] instead of [0-9] if you prefer

    Author Closing Comment

    Just what I needed!

    Thanks again sdstuber! Nice examples!

    Anything you don't have an answer for??? :-)
    LVL 36

    Expert Comment

    by:Geert Gruwez
    examining a logfile can be cumbersome
    >> did you check table v$archived_log ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    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…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now