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