Return value between two characters-CodePudding

I've got a code like:

SELECT config_id FROM config

Which returning me strings:

1S100I20C1P
1S8I9C1P
1S80I48C1P

I just need to get number between S and I, for 1 string its gonna be 100, for second string its gonna be 8 etc. I know regexp should work here, but can't really find the matching pattern. Thank You.

CodePudding user response：

This is, actually, the 2nd number in that string (result1). Or, use substr instr combination (result2):

SQL> with config (config_id) as
  2    (select '1S100I20C1P' from dual union all
  3     select '1S8I9C1P'    from dual union all
  4     select '1S80I48C1P'  from dual
  5    )
  6  select
  7    config_id,
  8    regexp_substr(config_id, '\d ', 1, 2) result1,
  9    substr(config_id, instr(config_id, 'S')   1,
 10                      instr(config_id, 'I') - instr(config_id, 'S') - 1
 11          ) result2
 12  from config;

CONFIG_ID   RESULT1     RESULT2
----------- ----------- -----------
1S100I20C1P 100         100
1S8I9C1P    8           8
1S80I48C1P  80          80

SQL>

CodePudding user response：

One option would be using REGEXP_REPLACE which contains letters S and I within the pattern such as

SELECT config_id,
       REGEXP_REPLACE(config_id,'(.*S)(.*)(I.*)','\2') AS extractedString
  FROM config

Demo