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

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

CodePudding user response：

[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

CodePudding user response：

You can use simple string functions (which may be more to type than with a regular expression but is likely to execute quicker):

SELECT SUBSTR(value, hash1   1, comma2 - hash1 - 1)
       || ',' ||
       SUBSTR(value, hash2   1, comma3 - hash2 - 1) AS parsed_value
FROM   (
  SELECT value,
         INSTR(value, '#', 1, 1) AS hash1,
         INSTR(value, '#', 1, 2) AS hash2,
         INSTR(value, ',', 1, 2) AS comma2,
         INSTR(value, ',', 1, 3) AS comma3
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT ',7809628#465797,7809628#461396,' FROM DUAL;

Outputs:

PARSED_VALUE

465797,461396

PARSED_VALUE
465797,461396

db<>fiddle here