Replacing Characters in a string

[John Bourne]

Hi All,

I am writing a function to replace a specific number of characters in a string.
These strings are in pairs - for example, I want '$' replaced by '*' and vice versa.

For example, I want select fn_replace_chars('a$r$a%q$',2); should output a*r*a%q$ as I want to replace 2 characters.

fn_replace_chars('a$r$a%q$',3) should output 'a*r*a%q*' and
fn_replace_chars('a$r$a%q$',4) should output 'a*r*a#q*'

I have written below function and I can't seem to get it right for the second parameter that controls how many characters are to be replaced.

I think I am missing something simple, but can't just point what.
Any help would be appreciated.


Here is the function:
-------------------------------------------------------------------------
DROP FUNCTION IF EXISTS fn_replace_chars;
DELIMITER |
CREATE FUNCTION fn_replace_chars( textvalue VARCHAR(10000) , chars_to_replace int) RETURNS VARCHAR(10000)
BEGIN

SET @outstring = textvalue;
SET @src = '$*%';
SET @dest = '*$#';
SET @src_count = LENGTH(@src);
SET @dest_count = LENGTH(@dest);
SET @str_count = LENGTH(@outstring);
set @src_loop=1;
set @str_loop=1;
set @replaced=0;
loop1:while @src_loop <= @src_count do
while @str_loop <= @str_count and @replaced <= chars_to_replace do
set @outstring=INSERT(@outstring, LOCATE(substr(@src,@src_loop,1), @outstring,@str_loop), 1, substr(@dest,@src_loop,1));
set @replaced=@replaced+1;
-- if @replaced >= chars_to_replace then
-- leave loop1;
-- end if;
set @str_loop=@str_loop+1;
end while;
set @src_loop = @src_loop + 1;
end while;
RETURN @outstring;
END
|
DELIMITER ;
--------------------------------------------------------------------

select fn_replace_chars('a$r$a%q$',2);