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
skahlert2010Asked:
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
0
 
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!
0
 
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)
0
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.

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

Thanks again sdstuber! Nice examples!

Anything you don't have an answer for??? :-)
0
 
Geert GOracle dbaCommented:
examining a logfile can be cumbersome
>> did you check table v$archived_log ?
0
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.