Oracle Regexp_Substr | Fetch String Between Two Delimiters

Oracle REGEXP_SUBSTR | Fetch string between two delimiters

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

Oracle Database, extract string beeing between two other strings

It's not working because the literal strings 'First' and 'Last' are being looked for. Assuming that the strings don't all literally begin 'First' you need to find another way to represent them. You've already done this by representing 'Middle' as (.*)

The next point is that you need to extract a sub-expression (the part in parenthesis), this is the 6th parameter of REGEXP_SUBSTR().

If you put these together then the following gives you what you want:

regexp_substr(source_string, '.*\s(.*)\s.*', 1, 1, 'i', 1)

An example of it working:

SQL> select regexp_substr('first middle last', '.*\s(.*)\s.*', 1, 1, 'i', 1)
  2    from dual;

REGEXP
------
middle

You can also use an online regex tester to validate that 'middle' is the only captured group.

Depending on what your actual source strings look like you may not want to search for exactly spaces, but use \W (a non-word character) instead.

If you're expecting exactly three words I'd also anchor your expression to the start and end of the string: ^.*\s(.*)\s.*$

REGEXP SUBSTR where delimiter is a combination of 2 or more characters

Not like that... Rather:

select REGEXP_SUBSTR('123$@45$6$@789','(.*?)(\$@|$)', 1, 2, null, 1) from dual;

Notice \$ in the code; $ means end of string, so if you mean a literal dollar sign symbol, you must escape it. This solution uses the "capturing group" concept - see the Oracle documentation for REGEXP_SUBSTR() if you are not familiar. The first capturing group is (.*?) - whatever comes before the delimiter; and it is referenced in the sixth (last) argument to REGEXP_SUBSTR.

Notice also that after the first capturing group I check for either the two-character delimiter or the end of the string... which is marked by $. Two options in parentheses and separated by | (another "special character") is the regular expression syntax for "either... or...".

Oracle SQL: Extracting text between two characters

We can use REGEXP_SUBSTR here:

SELECT regexp_substr('{ABCDE}{F1}', '\{([^}]+)\}', 1,1,NULL,1) AS output
FROM dual

Demo

This is a less commonly seen use of REGEXP_SUBSTR, which uses a capture group, per this pattern:

\{([^}]+)\}

The sixth parameter says to return the first capture group.

postgresql : regexp_substr - get sub string between occurrence of delimiters

regexp_replace(data::text,'^([^_]+_){3}','')

You can try

select regexp_replace(data::text,'^([^_]+_){3}','')
  from (
      select 'one_two_three_four  s'::text as data 
      union select 'a_bb_ccc_dddd_eeee_ffff'
  ) data

---

Related Topics