MySQL Forums
Forum List  »  Stored Procedures

Replacing Characters in a string
Posted by: John Bourne
Date: April 15, 2018 02:52AM

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;
CREATE FUNCTION fn_replace_chars( textvalue VARCHAR(10000) , chars_to_replace int) RETURNS VARCHAR(10000)

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;

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

Options: ReplyQuote

Written By
Replacing Characters in a string
April 15, 2018 02:52AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.