Pull values from a string-CodePudding

I have a column for which I need to pull the R- value.

What would be the best way to do this?

I feel like I'm close with this but having issues getting what I need

Basically need the R- value, can be different characters long, up until the Control lab.

See example below.

select SUBSTR( err_desc, 57, INSTR (err_desc, 'Control Lab:', 1, 1)-1)
from error_log
where sql_err_text = 'EXCEEDED VARIANCE LIMIT'
and year = '2022';

Example would be:

Plant: 649 Order: 2HC2204018 Year: 2022 Cycle: 01  Raw: R-66-59-18 Control Lab: WH Variance Warning: 50 Variance Limit: 100

Required output: R-66-59-18

Plant: 650 Order: 9GM2202004 Year: 2022 Cycle: 03  Raw: R-401059 Control Lab: GR

Required output: R-401059

Tried SQL above. Was getting more characters than expected.

CodePudding user response：

I would use REGEXP_SUBSTR() with a capture group:

SELECT err_desc, REGEXP_SUBSTR(err_desc, 'Raw: (R(-[0-9] ) )', 1, 1, NULL, 1) AS output
FROM error_log
WHERE sql_err_text = 'EXCEEDED VARIANCE LIMIT' AND year = '2022';

CodePudding user response：

Option with the substr instr combination; you have to find where Raw starts, and then subtract Control and Raw position (to find substring length). 5 (as well as -5) represents "Raw", colon and space. 1 is here to properly set substring length.

SQL> with error_log (err_desc) as
  2    (select 'Plant: 649 Order: 2HC2204018 Year: 2022 Cycle: 01  Raw: R-66-59-18 Control Lab: WH Variance Warning: 50 Variance Limit: 100' from dual union all
  3     select 'Plant: 650 Order: 9GM2202004 Year: 2022 Cycle: 03  Raw: R-401059 Control Lab: GR' from dual
  4    )
  5  select substr(err_desc, instr(err_desc, 'Raw')   5,
  6                          instr(err_desc, 'Control') - instr(err_desc, 'Raw') - 5   1
  7               ) result
  8  from error_log;

RESULT
--------------------------------------------------------------------------------
R-66-59-18 C
R-401059 C

SQL>