I have a string value in ColumnA separated by '_'.
| ColumnA | ColumnB |
|---|---|
| 1_2_AB34-E1 | 8 |
| 2_3_CD56-F1 | 9 |
I need to modify ColumnA by replacing the value after 2nd '_' with the value in ColumnB
| ColumnA | ColumnB |
|---|---|
| 1_2_8 | 8 |
| 2_3_9 | 9 |
I tried using REGEXP_REPLACE(ColumnA, '[^|]+', ColumnB, 1, 3). But it is not working as expected. Can someone share their inputs?
You could just take everything up to the second _ and then concatenate the other column:
select regexp_replace(a, '^([^_]+_[^_]+_).*', '\1') || b
If you want to modify the column in place, the logic works in an update:
update t
set a = regexp_replace(a, '^([^_]+_[^_]+_).*', '\1') || b;
Here is a db<>fiddle.
Assuming that every input string has at least two underscores, and that everything after the second underscore must be replaced, you can do this much more efficiently than with regular expressions - use standard string functions instead.
select substr(columnA, 1, instr(columnA, '_', 1, 2)) || columnB
from ...
(or use similar in update). instr returns the position of the second underscore in the input string in columnA, and then substr returns the substring from the first position up to and including that second underscore. Then concatenate columnB to that substring. The code follows the logic exactly, in every detail.
If the input string may sometimes have fewer than two underscores, you need to explain the requirement. The query above, in those cases only, will replace the entire string from columnA with the string from columnB - perhaps not the desired outcome. The query can be modified in those cases, to implement the required handling - while still being much more efficient than a regular expressions solution.