Oracle REGEXP_SUBSTR extracts strings by blank when there are multiple blanks

Question

Hi I'm new to Oracle SQL, I want to extract LiIon Polymer from 6Cell LiIon Polymer.

I use REGEXP_SUBSTR('6Cell LiIon Polymer', '\S+', 7) but it only returns LiIon

Answer 1

You want substring that follows the first space? Use good, old substr + instr combination. Sample data first, query you might be interested in begins at line #4.

SQL> with test (col) as
  2    (select '6Cell LiIon Polymer' from dual)
  3  --
  4  select substr(col, instr(col, ' ') + 1) result
  5  from test;

RESULT
-------------
LiIon Polymer

SQL>