CODEWITHSUNDEEP
regexselectconditional-statementsoracle12cregexp-replace
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

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

Upvotes: 1

Views: 119

Answers (1)

Ryszard Czech
Ryszard Czech

Reputation: 18611

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.

Upvotes: 1

Related Questions