REGEXP_SUBSTR: Need more precise regex code

Question

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.

As I said, I need to be able to use only the first 2 arguments of the regexp_substr function. — FoxArc
– FoxArc, Commented Mar 26, 2018 at 22:15
Why do you only want to use the first two arguments of the function? What is wrong with the rest of them? Obviously, you can extract the substring that matches '\d{5}\.' and then extract the five digits; two function calls instead of one. But WHY? What are you not telling us? — user5683823
– user5683823, Commented Mar 26, 2018 at 22:57

Wiktor Stribiżew · Accepted Answer · 2018-03-26 21:24:18Z

4

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.

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).

answered Mar 26, 2018 at 21:24

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

5 Comments

FoxArc Over a year ago

I know that this works, but I need something that doesn't use the other arguments. I need it to be completely encompassed by the second argument... somehow. We have several regular expressions that we use to get certain parts out of the string. My ultimate goal is make a table that holds the regular expression second argument so that when we get a new study, we don't have to always go and edit the stored procedures. We have a new study with a new format for the reference code that then needs to be parsed out to different parts...

user5683823 Over a year ago

@MacWilson08 - that makes no sense. If you must store function attributes in a table, then store values for ALL the attributes. The last one can be NULL when it's not needed; in this case it should be 1.

Wiktor Stribiżew Over a year ago

@MacWilson08 You can only set context using lookarounds (not available in Oracle regex) or with consuming patterns while capturing what you need to get (thus, the capturing group number argument is a must).

FoxArc Over a year ago

@WiktorStribiżew, that is going to be a wide table if I have to store the other arguments as well. I have 2 different types of code that must be parsed from one string. This is going to take some rethinking...

Gary_W Over a year ago

Most definitely NOT the right way for production, but in the meantime to get you through while you redesign, could you make your second parameter the rest of the args all together like "'(\d{5})\.', 1, 1, NULL, 1"? This assumes you are dynamically building the call to REGEXP_SUBSTR in code.

Collectives™ on Stack Overflow

REGEXP_SUBSTR: Need more precise regex code

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related