Oracle Regex Replace With Condition

Question

I'm new to use RegEx and I am trying to apply REGEXP_REPLACE with conditions if special character exists then apply regex else apply other regex for example

SELECT REGEXP_REPLACE ('PCB terminal block - FRONT 2,5-V/SA 5/10 BK - 1109601', '([^\-]+$)' , '')  FROM dual;

output

PCB terminal block - FRONT 2,5-V/SA 5/10 BK -

above regex remove after last (-) and it's OK

but if my string does not contains (-) then this will return null as below

SELECT REGEXP_REPLACE ('PCB terminal block, nominal current: 4 A, rated voltage (III/2): 250 V, nominal cross section', '([^\-]+$)' , '')  froM dual

output

null

i want to change this regex to return the string if it's not contains (-) is it possible?

GMB · Accepted Answer · 2020-03-23 09:57:07Z

The problem is that you original expression is too broad. It matches on a sequence of characters other than the dash (-) at the end of string: so if the string contains no dash, it matches entirely, and is suppressed.

You can add the dash to the regular expression, so it is part of the match. Unmatched strings are left untouched by REGEXP_REPLACE():

SELECT REGEXP_REPLACE (mycol, '-[^-]+$' , '-')  FROM dual;

Demo on DB Fiddle:

with t as (
    select 'PCB terminal block, nominal current: 4 A, rated voltage (III/2): 250 V, nominal cross section' mycol from dual
    union all select 'PCB terminal block - FRONT 2,5-V/SA 5/10 BK - 1109601' from dual
)
select mycol, regexp_replace(mycol,  '-[^-]+$' , '-') from t

MYCOL                                                                                         | REGEXP_REPLACE(MYCOL,'-[^-]+$','-')                                                          
:-------------------------------------------------------------------------------------------- | :--------------------------------------------------------------------------------------------
PCB terminal block, nominal current: 4 A, rated voltage (III/2): 250 V, nominal cross section | PCB terminal block, nominal current: 4 A, rated voltage (III/2): 250 V, nominal cross section
PCB terminal block - FRONT 2,5-V/SA 5/10 BK - 1109601                                         | PCB terminal block - FRONT 2,5-V/SA 5/10 BK -

Note that it is not required, nor wanted, to escape the dash in the character class.

Gordon Linoff · Accepted Answer · 2020-03-23 11:15:11Z

1

You can also do this using substitution:

regexp_replace(col,  '(^.*-)[^-]+$' , '\1')

The idea is to identify the part of the string that you want to keep -- that is the part in parentheses. The rest says "everything that is not a hyphen to the end of the string".

This works because if there is no match (i.e. no hyphen) then no substitution occurs and the entire string is returned.

answered Mar 23, 2020 at 11:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle Regex Replace With Condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related