Regular Expression

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
Who is Participating?
sdstuberConnect With a Mentor Commented:
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
skahlert2010Author Commented:
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!
sdstuberConnect With a Mentor Commented:
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)
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

you can use the posix class instead of range specifier in either of the solutions above
skahlert2010Author Commented:
Just what I needed!

Thanks again sdstuber! Nice examples!

Anything you don't have an answer for??? :-)
Geert GOracle dbaCommented:
examining a logfile can be cumbersome
>> did you check table v$archived_log ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.