'separating out numbers in Reg_exp (Oracle)
I have tried different variations on this query to no avail. To give context the numbers below are examples for contractions during labor. I am looking to parse out the first number and the 2nd if available- What I have so far below-I have it posted in the comments what I do & do not want. Essentially ID 1-3 parsed into separate numbers First number in ID 4 & 5-
Thank you in advance
with test as
(select 1 as ID,'2-4' as MEAS_VALUE from dual union all --want 2 & 4
select 2 as ID,'2.5-4' as MEAS_VALUE from dual union all --want 2.5 & 4
select 3 as ID,'2-4.5' as MEAS_VALUE from dual union all --want 2 & 4.5
select 4 as ID,'x2' as MEAS_VALUE from dual union all --want x2
select 5 as ID,'2 in 20 minutes' as MEAS_VALUE from dual) --want 2 but not 20
select test.*,
REGEXP_SUBSTR(MEAS_VALUE,'\d+.?([^-]\d)',1,1) as num1,
REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,2) as num2
from test;
Solution 1:[1]
Question says:
I am looking to parse out the first number and the 2nd if available
If so, then:
SQL> with test as
2 (select 1 as ID,'2-4' as MEAS_VALUE from dual union all --want 2 & 4
3 select 2 as ID,'2.5-4' as MEAS_VALUE from dual union all --want 2.5 & 4
4 select 3 as ID,'2-4.5' as MEAS_VALUE from dual union all --want 2 & 4.5
5 select 4 as ID,'x2' as MEAS_VALUE from dual union all --want x2
6 select 5 as ID,'2 in 20 minutes' as MEAS_VALUE from dual) --want 2 but not 20
7 select test.*,
8 regexp_substr(meas_value, '\d+(\.\d+)?', 1, 1) num1,
9 regexp_substr(meas_value, '\d+(\.\d+)?', 1, 2) num2
10 from test;
ID MEAS_VALUE NUM1 NUM2
---------- --------------- ---------- ----------
1 2-4 2 4
2 2.5-4 2.5 4
3 2-4.5 2 4.5
4 x2 2
5 2 in 20 minutes 2 20
SQL>
There's no rule that explains desired output for IDs 4 (x2; why x? It is not a digit) and 5 (no 20; why not? It is the 2nd digit here).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Littlefoot |
