Regular Expression To Return Number After Matched String In Oracle

I have a query: select ITEM_ID from system_items where id=4020; I want a regular expression that takes the above query as input and matches for pattern 'id=' and returns 4020. Ple

Solution 1:

REGEX_SUBSTR won't allow a look-behind like (?<=id=\s*)\d+ so I suspect you need to do this in two operations. First get id=4020, then strip the id=.

One possible way of doing that would be:

REGEXP_SUBSTR(REGEXP_SUBSTR(a, 'id=\s*\d+'), '\d+')

SQLFiddle

Solution 2:

This should do it

 /id=(\d+)/

idis literal match
() are used for making the capture groups
\d is more numbers 
+ ensures 1or more

demo here http://rubular.com/r/GBxfhID5hS