0

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

Osca
  • 1,588
  • 2
  • 20
  • 41
  • What do you really want? Anything after the first word? The last 2 words? Anything after the first word with a number in it? Anything after the first space? What if the first word's description was "6 cell" with a space in it? – Gary_W Aug 10 '20 at 19:43

2 Answers2

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

Using Regular expression and without hardcoding

select REGEXP_SUBSTR('6Cell LiIon Polymer', '[^\S]+', instr('6Cell LiIon Polymer', ' '), 1) from dual;
Atif
  • 2,011
  • 9
  • 23
  • The first space is being selected. I believe you want `REGEXP_SUBSTR('6Cell LiIon Polymer', '.*? (.*)$', 1, 1, NULL, 1)` – Gary_W Aug 12 '20 at 14:50
  • Oh yes, i missed it, It should be select REGEXP_SUBSTR('6Cell LiIon Polymer', '[^\S]+', instr('6Cell LiIon Polymer', ' ') + 1, 1) from dual; – Atif Aug 12 '20 at 15:15