CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
Bruce
Bruce

Reputation: 91

Oracle SQL REGEXP to replace a particular string with different column value

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?

Upvotes: 0

Views: 3652

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 3

user5683823
user5683823

Reputation:

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.

Upvotes: 1

Related Questions