How to make substring with the first and third occurrence of a character in oracle11g?

Question

I have texts like below in a column-

a|bbb|cc|dddd
d||ff|gg
a|zzzz|pp|rrr

I want to display the text between 1st "|" and 2nd "|" symbols. Below given should be the output.

Output
------
bbb
null
zzzz

Tried with below query, but it gives me 'ff' instead of null.

SELECT regexp_substr('d||ff|gg','[^|]+',1,2) regexp_substr FROM dual;

How should I go about doing this?

score 0 · Answer 1 · answered May 15 '18 at 22:27

This answer explains why what you are doing doesn't quite work, and how to modify it. You need a slightly more complicated pattern, including escaping the pipe delimiter; and additional arguments to the function call:

SELECT regexp_substr('d||ff|gg','(.*?)(\||$)',1,2,null,1) regexp_substr FROM dual;

REGEXP_SUBSTR
--------------------
(null)

Or with your other sample rows in a CTE:

with t (str) as (
  select 'a|bbb|cc|dddd' from dual
  union all select 'd||ff|gg' from dual
  union all select 'a|zzzz|pp|rrr' from dual
)
select str,
  regexp_substr(str,'(.*?)(\||$)',1,2,null,1) as str2
from t;

STR           STR2
------------- --------------------
a|bbb|cc|dddd bbb
d||ff|gg      (null)
a|zzzz|pp|rrr zzzz

How to make substring with the first and third occurrence of a character in oracle11g?

1 Answers1