In an Oracle database I have a string such as 12.13 this is a test. Using regexp_substr I'm trying to extract just 13. That is the number after the dot and before the space. I've tried various combinations and (string, '\.[0-9]{1,}') comes closest but I still get .13. Is there any way to just return 13?
The data also includes items like 1.1. At most the numbers will go 99.99.
All possible number formats are: #.#, #.##,##.#,##.##
Given the input strings will always have leading numbers and a dot, one can extract the fractional numbers with regexp_substr. regexp_replace could also be used.
Here are a few examples for regexp_substr. These work by including a capturing group targeting the desired data, and a subexpression argument (the last argument in regexp_substr):
SELECT REGEXP_SUBSTR('12.13 this is a test','^[0-9]{1,}[.]{1}([0-9]{1,})',1,1,NULL,1) AS FRACTIONAL FROM DUAL;
Result:
FRACTIONAL
13
1 row selected.
SELECT REGEXP_SUBSTR('1.1 this is a test','^[0-9]{1,}[.]{1}([0-9]{1,})',1,1,NULL,1) AS FRACTIONAL FROM DUAL;
Result:
FRACTIONAL
1
1 row selected.
SELECT REGEXP_SUBSTR('99.99 this is a test','^[0-9]{1,}[.]{1}([0-9]{1,})',1,1,NULL,1) AS FRACTIONAL FROM DUAL;
Result:
FRACTIONAL
99
1 row selected.
I like regexp_replace better than regexp_substr for such tasks.
regexp_replace(str, '^.*?\.([[:digit:]]+).*$', '\1');
i.e. look for the wohle string from the start, with some characters, a point, some digits, some characters until the end, but remember only the digits and return those instead of the string.
But I guess that's just a matter of personal preference.
select regexp_substr('12.13 this is a test', '\d+\.(\S+)', 1, 1, null, 1) rs
from dual;
RS
--
13
