时间戳

首页 > 解决方案 > Oracle Regex Replace With Condition

sql - Oracle Regex Replace With Condition

问题描述

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?

标签： sqlregexoracle

解决方案

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.

推荐阅读