I need to replace a particular character of one in oracle.
For example:
cadena= '12121113'
and what I need is to change the "1" to a "2" but only the "1" that is in the 3 position.
and return the modified result.
As a result, there should be '12221113' .
I hope you have explained me well.
With this query you will see in the third "case when" the value equals 1.
If the result is true, then the first part of the substring is concatenated, then to be concatenated, a part that is to be changed and then concatenated with the end of its string.
If it is false, then the true value will be returned.
select
case when substr('12121113', 3, 1) = 1
then substr('12121113', 1, 2) || replace(substr('12121113', 3, 1), '1', '2') || substr('12121113', 4)
else '12121113'
end as modified
from dual
As an alternative I leave the following code. you can use instr
instr('12121113','1',2)
and the query would be the following
select substr('12121113',1,instr('12121113','1',2)-1)||
replace(substr('12121113',instr('12121113','1',2),1),'1','2')||
substr('12121113',instr('12121113','1',2)+1,length(12121113))
from dual