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+)

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

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

• regex
• oracle-database

• 7 Try it like this ^:25:|- and replace with an empty string regex101/r/hhFvBb/1 – The fourth bird Commented Mar 4 at 9:11
• 2 What you are doing is correct and the only way to do that in a general case. You cannot match and remove within a single match operation. "Is there any way to do the replace also with same regex" - see stackoverflow/a/7769529/3832970. – Wiktor Stribiżew Commented Mar 4 at 9:23
• @Thefourthbird - Thanks for your response. Any way to put it in a capture groups? Unfortunately in oracle, we have to specify a capture group. I tried with and without capture, both way, with 1 capture group and without, but I am getting only hyphen - in output. – Utsav Commented Mar 4 at 9:31
• 1 Use regexp_replace(txt, '(:25:|-)'). Demo: dbfiddle.uk/BErQjaAo – Thorsten Kettner Commented Mar 4 at 10:09
• 1 @Thefourthbird Added a community wiki answer with your solution - feel free to add anything to it that you think needs adding to clarify the answer. – MT0 Commented Mar 4 at 11:38

1 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:

fiddle