0

lets say we have table as

with table1 
as
(
select 'CS issue-result info' col1
from dual
union all
select 'ITP decile info' col1
from dual
union all
select 'DFSD fdb-quentile-info' col1
from dual
union all
select 'EUR transcription info' col1
from dual
union all
select 'ABPK stability control info' col1
from dual
)
select *
from table1;

i.e. enter image description here

my expected output is as below

col1
issue-result info
decile info
fdb-quentile-info
transcription info
stability control info

enter image description here

condition : from the string preceding part of first space should be removed

Anil Tiwari
  • 71
  • 8
  • What is the desired result if there is no space at all in the input string? (Alternatively, if that is never possible, you need to say that explicitly when you state the problem.) –  Jun 14 '21 at 13:12

3 Answers3

0

I think you want to remove the first "element" of the string. For this, you can use regexp_replace():

select t.*, regexp_replace(col1, '^[^ ]+ ', '')
from table1 t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You may try using REGEXP_SUBSTR with a capture group:

SELECT REGEXP_SUBSTR(col1, ' (.*)$', 1, 1, NULL, 1) AS col1
FROM table1;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Thank you for test case!

I wouldn't do that with regular expressions; for large data set, that's probably slower than a simple substr + instr combination:

 <CTE here>
 19  select substr(col1, instr(col1, ' ') + 1) result
 20  from table1;

RESULT
---------------------------
issue-result info
decile info
fdb-quentile-info
transcription info
stability control info

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This runs into problems should the `col1` value happen to be just a single word (my answer also fails in this regard). – Tim Biegeleisen Jun 14 '21 at 12:56
  • Right, @Tim, but - that's not what the OP's test case suggests, and there's no evidence that such data exist. If so, we'll have to modify our queries. – Littlefoot Jun 14 '21 at 12:58