0

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360

1 Answers1

0

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.
Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Hi Gary, thank you so much for this. This is absolutely what I'm looking for. Is there a way where I can shout you a coffee or something for this? – Jonathon Chau Jul 14 '22 at 23:02
  • @JonathonChau Just the fact that you want to do that is thanks enough! Happy to help. Oh and mark the answer as accepted to show future searchers it helped you :-) – Gary_W Jul 15 '22 at 12:19
  • Hi Gary, just accepted it. Cheers. – Jonathon Chau Jul 19 '22 at 06:26