How to extract a value inside column in certain pattern in oracle

February 23, 2022

I want to extract data in the format (465797,461396) from (‘,7809628#465797,7809628#461396,’) data. How can I do that?

>Solution :

[DBFiddle][1]

select
  ltrim(
    regexp_replace(
        ',7809628#465797,7809628#461396,'
       ,'[^#]*#(\d+),'
       ,',\1')
    ,','
  ) new_str
from dual;

Results:

NEW_STR
-------------
465797,461396

Regexp '[^#]*#(\d+),':

[^#]* – any number of any symbols except ‘#’
(\d+) – digits, one or more digits, () – marks it as a group
and comma after that.

So it finds (any symbols except #, then #, then one or more digits, then comma) and replaces all found substrings to comma and found "one or more digits"(the only group in the expression). Then ltrim removes extra comma in the beggining.
[1]: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f66505cd2746a1387e41ec91d6c7df3a