Search code examples
regexselectconditional-statementsoracle12cregexp-replace

To trim particular value in a column

I have a regular expression select statement like below :

SELECT REGEXP_REPLACE(nvl(l.text_1, l.text),'^'||21810||'=|\|'
                                    ||21810||'=', '|'||21810||'='||'B1') 
                                    FROM table_1 1

This checks the value of texts and add's B1 if the text has 21810

eg: If my text is 21614=C1||21810=C2

what it does is : 21614=C1|||||||21810=B1C2

But I want this to remove this C1 and C2 which is always followed by a '=' and just add B1 after '=' in 21810

 21614=|||||||21810=B1
Solution

  • Use a two-step replacement:

    1. Remove values after = and before |:
      Replace [^=|]+(\||$) with \1. See regex proof.

    2. Next, add B1:
      (^|\|)21810= with \121810=B1. See this regex proof.