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-28 16:40:52Z

6

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, 2018 at 16:40

answered Feb 28, 2018 at 10:35

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amit Jagtap Over a year ago

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.

Kaushik Nayak Over a year ago

@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?

Amit Jagtap Over a year ago

There are only two cases: Organization, INC..Truck/Equipment Failure |C and Organization, INC..Truck/Equipment Failure.

Kaushik Nayak Over a year ago

@AmitJagtap : You are welcome. It is always better to include all cases to expect while asking the question to avoid confusion.

Nick Over a year ago

hi is there any way to this in oracl 10g

Littlefoot · Accepted Answer · 2018-02-28 10:35:37Z

2

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>

answered Feb 28, 2018 at 10:35

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

Oracle REGEXP_SUBSTR | Fetch string between two delimiters

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related