0

ORACLE:SQL REGEXP_SUBSTR that returns the column value after 3rd semicolon and before the pipe whose value starts with D

example:

column value: 'D:5:testps:12345|blah blah/blah'

expected value: 12345

regex that would filter values which start with D and returns value after 3rd semicolon and before pipe

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
dreambigcoder
  • 1,859
  • 4
  • 22
  • 32

2 Answers2

2
select column_value,
   regexp_substr(column_value, '([^:]*:){3}([^|]*)\|', 1, 1, null, 2) as str
from (
       select 'D:5:testps:12345|blah blah/blah' as column_value from dual union all
       select 'XD:5:testps:12345|blahblah/blah' as column_value from dual
     )
where column_value like 'D%'
;

COLUMN_VALUE                      STR
-------------------------------   -----
D:5:testps:12345|blah blah/blah   12345
0

You can use regex_replace:

select case when col like 'D%' 
            then regexp_replace(col, '^([^:]*:){3}(\d+)\|.*', '\2') num
       end
from t;

Produces:

12345

it produces null if the col doesn't start with D

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • What will this return for a string that does NOT begin with a D? (I think the OP didn't want anything returned - not even NULL.) NULL is still a valid return from a string that DOES begin with D! –  Feb 24 '17 at 16:41