CODEWITHSUNDEEP
sqlregexoracle-database
Erkko Kõrgema
Erkko Kõrgema

Reputation: 19

ORACLE regexp_substr extract everything after specific char

How to get rest of string after specific char?

I have a string 'a|b|c|2|:x80|3|rr|' and I would like to get result after 3rd occurance of |. So the result should be like 2|:x80|3|rr|

The query

select REGEXP_SUBSTR('a|b|c|2|:x80|3|rr|','[^|]+$',1,4) 
from dual

Returned me NULL

Upvotes: 1

Views: 7086

Answers (5)

Deep
Deep

Reputation: 382

Erkko, You need to use the combination of SUBSTR and REGEXP_INSTR OR INSTR.

Your query will look like this. (Without Regex)
SELECT SUBSTR('a|b|c|2|:x80|3|rr|',INSTR('a|b|c|2|:x80|3|rr|','|',1,3)+1) from dual;

Your query will look like this. (With Regex as you want to use) SELECT SUBSTR('a|b|c|2|:x80|3|rr|',REGEXP_INSTR('a|b|c|2|:x80|3|rr|','\|',1,3)+1) from dual;

Explanation:

First, you will need to find the place of the string you want as you mentioned. So in your case | comes at place 6. So that +1 would be your position to start to substring.

Second, from the original string, substring from that position+1 to unlimited.(Where your string ends)

Example:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6fd782db95f575201eded084493232ee

Upvotes: 0

Ilia Maskov
Ilia Maskov

Reputation: 1898

If you use oracle 11g and above you can specify a subexpression to return like this:

select REGEXP_SUBSTR('a|b|c|2|:x80|3|rr|','([^|]+\|){3}(.+)$',1,1,null,2) from dual

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

REGEXP_REPLACE() will do the trick. Skip 3 groups of anything followed by a pipe, then replace with the 2nd group, which is the rest of the line (anchored to the end).

SQL> select regexp_replace('a|b|c|2|:x80|3|rr|', '(.*?\|){3}(.*)$', '\2') trimmed
  2  from dual;

TRIMMED
------------
2|:x80|3|rr|

SQL>

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

I suggest a nice by long way by using regexp_substr, regexp_count and listagg together as :

select listagg(str) within group (order by lvl) 
       as "Result String"
  from
(
  with t(str) as
  (
   select 'a|b|c|2|:x80|3|rr|' from dual
  )
  select level-1 as lvl,
         regexp_substr(str,'(.*?)(\||$)',1,level) as str 
    from dual
    cross join t
   connect by level <= regexp_count('a|b|c|2|:x80|3|rr|','\|')
)
where lvl >= 3;

Rextester Demo

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

Use SUBSTR / INSTR combination

WITH t ( s ) AS (
     SELECT 'a|b|c|2|:x80|3|rr|'
     FROM dual
) SELECT substr(s,instr(s,'|',1,3) + 1)
  FROM t;

Demo

Upvotes: 2

Related Questions