Search code examples
regexoracle-databaseregexp-replace

Oracle Regex_replace to delete a pattern and following 3 characters

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:

  1. 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.

  1. 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.*
Solution

  • 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.