Oracle REGEXP_SUBSTR | Fetch string between two delimiters

Question

I have a string Organization, INC..Truck/Equipment Failure |C. I want to fetch the sub-string after organization name (after two '..' characters) and before pipe character. So the output string should be - Truck/Equipment Failure. Can you please help.

I have been trying forming regexp like this but doesn't seem working.

select regexp_substr('Organization, INC..Truck/Equipment Failure |C', '[^.]+',1,2) from dual;

Kaushik Nayak · Accepted Answer · 2018-02-28T16:40:52.367

7

You may use this.

SELECT REGEXP_SUBSTR ('Organization, INC..Truck/Equipment Failure |C',
                      '([^.]+)\|',
                      1,
                      1,
                      NULL,
                      1)
  FROM DUAL;

EDIT: This will match exactly two dots followed by one or more characters other than a | till the end of string.

SELECT REGEXP_SUBSTR ('Organization, INC..Truck/Equipment Failure',
                      '\.{2}([^|]+)',
                      1,
                      1,
                      NULL,
                      1)
  FROM DUAL;

DEMO

edited Feb 28 '18 at 16:40

answered Feb 28 '18 at 10:35

Kaushik Nayak

30,772
5
32
45

Thanks Kaushik for quick response. This works but there is one problem. If the input string is `Organization, INC..Truck/Equipment Failure`, that is, if it is without pipe substring, it returns NULL. In that case it should return `Truck/Equipment Failure`. Can you please help with this. – Amit Jagtap Feb 28 '18 at 11:18
@AmitJagtap : So it means in some cases you don't have pipe. What should we rely on then? end of string ? Are these two the only cases or do you have any other format? – Kaushik Nayak Feb 28 '18 at 11:31
There are only two cases: `Organization, INC..Truck/Equipment Failure |C` and `Organization, INC..Truck/Equipment Failure`. – Amit Jagtap Feb 28 '18 at 11:35
2

@AmitJagtap : You are welcome. It is always better to include all cases to expect while asking the question to avoid confusion. – Kaushik Nayak Feb 28 '18 at 11:52
hi is there any way to this in oracl 10g – Nick Apr 27 '20 at 09:11

score 2 · Answer 2 · answered Feb 28 '18 at 10:35

Classic SUBSTR + INSTR option:

SQL> with test as (select 'Organization, INC..Truck/Equipment Failure |C' col from dual)
  2  select substr(col, instr(col, '..') + 2,
  3                     instr(col, '|') - instr(col, '..') - 2
  4                ) result
  5  from test;

RESULT
------------------------
Truck/Equipment Failure

SQL>

Oracle REGEXP_SUBSTR | Fetch string between two delimiters

2 Answers2

Linked

Related