As you ask specifically for a RegEx solution, let’s start with
) as checked,
) as suggested,
) 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
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.:
REPLACE(REPLACE(nvl(text_1, text),'C1',''), 'C2', '') || 'B1' as suggested,
REPLACE(REPLACE(nvl(text_1, text),'C1','|||||'), 'C2', '') || 'B1' as suggested2
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.