Return value between two characters

Question

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.

Does this answer your question? [Oracle REGEXP\_SUBSTR | Fetch string between two delimiters](https://stackoverflow.com/questions/49027346/oracle-regexp-substr-fetch-string-between-two-delimiters) — astentx, Oct 25 '21 at 10:00

score 4 · Answer 1 · answered Oct 25 '21 at 09:13

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>

score 0 · Answer 2 · answered Oct 25 '21 at 11:32

0

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

answered Oct 25 '21 at 11:32

Barbaros Özhan

59,113
10
31
55

Return value between two characters

2 Answers2