CODEWITHSUNDEEP
sqlregexoracle-database
Jaggu
Jaggu

Reputation: 23

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.

Please let me know if you have any suggestions, as I have been trying with REGEXP_SUBSTR in Oracle and couldn't get it.

Upvotes: 0

Views: 1890

Answers (2)

OGHaza
OGHaza

Reputation: 4795

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

Upvotes: 1

aelor
aelor

Reputation: 11116

This should do it

 /id=(\d+)/

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

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

Upvotes: -1

Related Questions