To trim particular value in a column in Oracle sql

Question

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

Answer 1

As you ask specifically for a RegEx solution, let's start with

SELECT
 text_1,
 text,
 REGEXP_REPLACE(nvl(l.text_1, l.text),
 '^'||21810||'=|\|'||21810||'=',
 '|'||21810||'='||'B1'
 ) as checked,
 REGEXP_REPLACE(nvl(l.text_1, l.text),
 '^(\d+=)C1(\|\|21810=)C2',
 '1円2円B1'
 ) as suggested,
 REGEXP_REPLACE(nvl(l.text_1, l.text),
 '^(\d+=)C1(\|\|21810=)C2',
 '1円|||||2円B1'
 ) as suggested2
FROM table_1 l
;

Not quite sure, how you ended up with the RegEx in your question. However, you are searching for ^21810=|\|21810= (21810= at the start of the string, or |21810= anywhere in the string) and replacing the match with |21810=B1.

My attempt checks the whole string, and requires

• one or more numbers followed by an equal sign at the beginning of the string (preserved for later reference),
• followed by C1 (which is not preserved for later),
• followed by ||21810= (preserved), and
• C2 ending the string (not preserved).

The preserved parts plus B1 are returned.

However, starting from your requirements as stated and your sample data, you could work as well with, e.g.:

SELECT
 text_1,
 text,
 REPLACE(REPLACE(nvl(text_1, text),'C1',''), 'C2', '') || 'B1' as suggested,
 REPLACE(REPLACE(nvl(text_1, text),'C1','|||||'), 'C2', '') || 'B1' as suggested2
FROM table_1
WHERE instr(nvl(text_1, text), '21810') > 0
;

It filters the strings to change via the WHERE clause, drops the unwanted parts with empty replacements, and adds B1 at the end.

NB: Your sample output includes five additional pipe characters. These are missing from your stated requirements. I provided both options for your reference.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.

• Sangathamilan Ravichadnran: Hi, Thanks but the C1 and C2 values are not always the same it can be any value that is after the =
• Abecee: (i) So C1 and C2 are placeholders? How about 21614 and 21810 - are these fix, or could they change as well? (If so, would they always be numeric?) (ii) Could any of the terms on either side of the equal sign themselves include an equal sign? If so which do / do not? (iii) Does your expected output include two or seven pipe characters? (iv) Does the sample input represent the whole column value - or is it part of a longer string?
• Abecee: @newintodev Please provide more detail in order to get an answer better tailored to your problem at hand.