regexp-substr

regexp_substr in sql to separate numbers from text field


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

Solution

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