Oracle Regex_replace to delete a pattern and following 3 characters

Question

I'm trying to do the following operation. I have many lines of data that needs to be edited to delete a part of a string. This string contains a pattern B0ExxB+, where 'x' could be any character or number.

Input example (string) : '1SX8+B0DSUB+B0E0LB+B0FMAB+B0G0KB'
Desired output : '1SX8+B0DSUB+B0FMAB+B0G0KB'

I use the REGEXP_REPLACE operation. have two strategies to accomplish the operation:

Known the pattern B0E apply more regex operators to pick also the 4 following characters after the pattern, and then replace them by nothing.
- Main idea.
update rx3qtxin xn set cin.cin_value = REGEXP_REPLACE (cin.cin_value, '\SB0E', '') where cin.id = 500228;

That deletes the pattern, OK. I've tried with adding as many ? after the pattern but it delete any other part of the string.

Known left pattern (B0E) and right pattern (B+) delete where both patterns are matched and the content between them.
- No idea how to apply that idea in regex.*

Please rephrase your question by showing clear input with expected output. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 3, 2020 at 13:54
What's with the \S in the regular expression in your attempt? There is no mention of it in the problem description. — user5683823
– user5683823, Commented Mar 3, 2020 at 14:12
Jorge, do you mean you want to avoid matching BBExxB+? Try REGEXP_REPLACE('1SX8+B0DSUB+B0E0LB+B0FMAB+B0G0KB', '(^|\+)B0E[[:alnum:]]{2}B\+(B0E[[:alnum:]]{2}B\+)*', '\1') then (it also matches consecutive occurrences). — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 3, 2020 at 14:33
After your edit, does it mean my first suggestion worked for you? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 3, 2020 at 14:58

user5683823 · Accepted Answer · 2020-03-03 14:08:05Z

4

The question mark is a placeholder for a single character in Unix ("wildcard character"). In regular expressions, for the same task we use the dot (period). The question mark (in regular expression) just makes the subexpression it applies to optional. It won't help you with your task.

You need something like

regexp_replace(col, 'B0E..B\+')

Note that the "plus sign" must be escaped, since unescaped it is a metacharacter.

answered Mar 3, 2020 at 14:08

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Jorge Vega Sánchez Over a year ago

Works perfect. I thought i need a regex operator before the pattern.

Collectives™ on Stack Overflow

Oracle Regex_replace to delete a pattern and following 3 characters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related