I have a sql that returns comments based on employee feedback.
As you can see with the comments below, the formatting can be a bit different.
Is there a way that i can extract the numbers out?
Examples :
W.C. 06.07.2022 change from 7 to 5
wk com 13/07 demand 8 change to 13
Increase demand from 7 to 12 W/C 11/07
Output Result
7 and 5,
8 and 13,
7 and 12
Here's a way given the sample data. First identify the group of 1 or more numbers followed by an optional group of the word of "change" and a space, followed by the word "to and a space, then 1 or more digits. Within that group, group the digits desired. Of course, big assumptions here on the words between the numbers.
WITH tbl(ID, emp_comment) AS (
SELECT 1, 'W.C. 06.07.2022 change from 7 to 5' FROM dual UNION ALL
SELECT 2, 'wk com 13/07 demand 8 change to 13' FROM dual UNION ALL
SELECT 3, 'Increase demand from 7 to 12 W/C 11/07' FROM dual
)
SELECT ID, REGEXP_SUBSTR(emp_comment, '.* ((\d+) (change )?to \d+).*', 1, 1, NULL, 2) nbr_1,
REGEXP_SUBSTR(emp_comment, '.* (\d+ (change )?to (\d+)).*', 1, 1, NULL, 3) nbr_2
FROM tbl;
ID NBR_1 NBR_2
---------- ----- -----
1 7 5
2 8 13
3 7 12
3 rows selected.