2

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.

FoxArc
  • 114
  • 9
  • As I said, I need to be able to use only the first 2 arguments of the regexp_substr function. – FoxArc Mar 26 '18 at 22:15
  • 1
    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? –  Mar 26 '18 at 22:57

1 Answers1

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.

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

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • 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... – FoxArc Mar 26 '18 at 22:11
  • 1
    @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`. –  Mar 26 '18 at 23:01
  • @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). – Wiktor Stribiżew Mar 26 '18 at 23:13
  • @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... – FoxArc Mar 27 '18 at 13:29
  • 1
    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. – Gary_W Mar 28 '18 at 16:42