CODEWITHSUNDEEP
regexoracle-databaseoracle11gregexp-substr
FoxArc
FoxArc

Reputation: 114

REGEXP_SUBSTR: Need more precise regex code

I'm trying to find a way to have one regular expression code to only get the 5 digits right before the .(dot). I've tried so many things and can't seem to get the 99999... I'd like to do this without adding any other arguments.

SELECT regexp_substr('ffffffff-5fd1-456b-8f4c-4a6ecc3b7cba::11899999.99', '[118]\d+') 
  FROM DUAL;

I'm getting 1189999 currently, and have gotten 118999 when I do '[118]\d{5}' or '[118]\d{5}+'...

Help... Please... Thank you.

Upvotes: 2

Views: 199

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627020

You may capture the 5 digits before a dot:

regexp_substr('ffffffff-5fd1-456b-8f4c-4a6ecc3b7cba::11899999.99', '(\d{5})\.', 1, 1, NULL, 1)

See the Oracle demo.

enter image description here

The (\d{5}) is a capturing group that matches 5 consecutive digits and stores the value in Group 1, and \. just matches a dot (as a context). The last argument to regexp_substr function is the number of the capturing group (here, the only one that is the first).

Upvotes: 4

Related Questions