I need a sql query, from the string ( 'abcdaefgahj' ), where I have to change the 3rd occurance of 'a' with replaced by 'o'. output will be look like ('abcdaefgohj') Can somebody please help me to write a SQL query.

First Way:
Oracle has provided a simple solution for this requirement. There is
no need to write lengthy queries.


SELECT REGEXP_REPLACE('abcdaefgahj', 'a','o', 1, 3) RESULT
FROM dual;

Here this query can be modified based on your requirements.


REGEXP_REPLACE(<Your String>, String to find,String to replace, Starting
position , Occurrence of the string to be found)

Hope this helps.


Second Way:
Please create the function and the use the below query

create or replace function replace_3_occurance_char (str varchar,
char_to_replace varchar2,replace_char varchar2)
return varchar2
is
l_no_occurance number;
s varchar2(2000);
flag varchar2(1);
str_length number;
begin
flag := 'N';
s:= str;
for i in 1..3
loop
select   instr(s,char_to_replace) into l_no_occurance from dual;
if l_no_occurance !=0 then
s:=substr(s,l_no_occurance+1,

length(str));
else
flag := 'N';
exit;
end if;
flag := 'Y';
str_length := length(str)-length(s);
end loop;
If flag ='Y' then

return substr(str,1,str_length-1) || replace_char || s;
else
return 'No three occur';
end if;
end;

select replace_3_occurance_char( 'abcdaefgahj','a','o') from dual

select replace_3_occurance_char( 'aaao','a','o') from dual

 

Third Way:

SELECT CONCAT (CONCAT (SUBSTR ('abcdaefgahj',
                              1,
                              (INSTR ('abcdaefgahj', 'a', 1, 3)) - 1
                             ),
                      'o'
                     ),
              SUBSTR ('abcdaefgahj',
                      (INSTR ('abcdaefgahj', 'a', 1, 3) + 1),
                      LENGTH ('abcdaefgahj')
                     )
             ) test
 FROM DUAL