CODEWITHSUNDEEP
regexstringoracle-database
filippo
filippo

Reputation: 5803

Remove prefix and suffix with oracle regex

I have some records with that come with a prefix and suffix (may or may not come). I'm trying to figure the REGEXP_REPLACE that will always return me a parametrized value.

My attempt so far is:

with teste as (
select '[email protected]' as num from dual
union
select '[email protected]' as num from dual
union
select '006789' as num from dual
union
select '+9876' as num from dual
union
select '[email protected]' as num from dual
union
select '123456789' as num from dual
)
select REGEXP_REPLACE(num,'^00(.*)\@.*$|^\+(.*)\@.*$','\1') from teste

but is not quite there.

The output of that should be:

num
12345
54321
6789
9876
13579
123456789

Upvotes: 3

Views: 5068

Answers (1)

stema
stema

Reputation: 93086

Try this one here

REGEXP_REPLACE(num,'^(00|\+)?(\d*)(\@.*)?$','\2')

See it here online at Regexr

I am not sure what Oracle regex is able to do. Critical points could be the \d meaning a digit, if this is not working replace \d with [0-9].

(?:) are non capturing groups. The pattern inside is not stored in a capturing group so you can always replace with the first capturing group \1

I also changed from your alternatives using | to optional parts using ? after the non capturing groups. The two brackets in your "OR" caused that the result is sometimes in group 1 (when the first alternative is matching) and sometimes in group 2 (when the second alternative is matching)

Upvotes: 4

Related Questions