Replace one string with another.

drop routine if exists StringReplace;
create function StringReplace(aStr varchar(50), 
findstr varchar(50),
replacestr varchar(50))
RETURNS varchar(50)
  declare apos int;
  declare alen int;
  set apos = position(findstr in aStr);
  set alen = char_length(findstr);
  if replacestr is NULL then
    set replacestr = '';
    END if;
  while apos > 0 do
     set aStr = Substring(aStr from 1 for apos - 1) + replacestr +
     Substring(aStr from apos+alen for char_length(aStr)-alen-apos+1);
     set apos = position(findstr in aStr);
  end while;
  return aStr;
How to use?: 

It should work with any string value (including ''), but passing an uncasted NULL through a parameter won't work and lead to an error. Use coalesce when you pass in a parameter or field
that could be NULL.

SQL Functions & Procedures
Current Version: 
Supported Products: 
NexusDB V2
NexusDB V3