Merging of regex and replace

Question

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),'-','')

Answer 1

@TheFourthBird proposed a solution in comments

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

• ^:25: matches the start of the string ^ followed by :25:.
• - matches hyphens anywhere in the 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