Regular expression to return number after matched string in oracle

Question

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.

OGHaza · Accepted Answer · 2014-02-18 12:52:59Z

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

edited Feb 18, 2014 at 12:52

answered Feb 18, 2014 at 10:29

OGHaza

4,7957 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aelor · Accepted Answer · 2014-02-18 09:24:05Z

-1

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

answered Feb 18, 2014 at 9:24

aelor

11.2k3 gold badges35 silver badges48 bronze badges

4 Comments

Jaggu Over a year ago

Hi aelor, I have tried it in this way DBMS_OUTPUT.PUT_LINE(REGEXP_SUBSTR('select ITEM_ID from system_items where id=4020;','/id=(\d+)/')); It doesn't give any output :(

aelor Over a year ago

Can you try this DBMS_OUTPUT.PUT_LINE(REGEXP_SUBSTR('select ITEM_ID from system_items where id=4020;','\d+$'));

Jaggu Over a year ago

Aelor, say if the query is "select ITEM_ID from system_items where id=4020 order by field1;" still this could work ?

aelor Over a year ago

i think you need to remove the start and end '/' in my regex

Collectives™ on Stack Overflow

Regular expression to return number after matched string in oracle

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related