regexoracle-database

Merging of regex and replace


I am using Oracle, but my question is general.

I have a text field like below

:25:3500011-01-3 

or

:25:74888332223

There can be 0 or any number of hyphens (-) in between the numbers.

I want to extract the number after :25: without the hyphens.

So output should look like

3500011013

or

74888332223

this can be achieved partially, with hyphens, by below regex (:25:)(\S+)

https://regex101.com/r/dOQizi/2

But I want to remove the hyphens also. For that currently I am using replace function in Oracle on top of regex. But as it has to be executed multiple times, I am wondering if it can be extracted by 1 regex itself.

Currently I am using below in Oracle. Is there any way to do the replace also with same regex.

replace(regexp_substr(column_name,'(:25:)(\S+)',1,1,'i',2),'-','')


Solution

  • @TheFourthBird proposed a solution in comments

    Try it like this ^:25:|- and replace with an empty string

    You can use it with REGEXP_REPLACE:

    SELECT txt,
           REGEXP_REPLACE(txt, '^:25:|-') as num
    FROM   table_name;
    

    Which, for the sample data:

    CREATE TABLE table_name (txt) AS
      select ':25:3500011-01-3' as txt from dual union all
      select ':25:74888332223' as txt from dual;
    

    Outputs:

    TXT NUM
    :25:3500011-01-3 3500011013
    :25:74888332223 74888332223

    fiddle