create procedure lib_string_replace (
                     strg_in varchar( 32765),
                     strg_beg smallint,
                     strg_search varchar( 32765),
                     strg_replace varchar( 32765))
                   returns (
                     strg_out varchar( 32765),
                     strg_replaced smallint)
  as
    declare variable strg_comp varchar( 32765);
    declare variable strg_pre varchar( 32765);
    declare variable strg_pst varchar( 32765);
    declare variable strg_len_orig smallint;
    declare variable strg_len_srch smallint;
    declare variable strg_len_repl smallint;

    begin
      /*
        replace substring
          str_beg : 1 ..

        examples:
          execute procedure lib_string_replace :strg_in, 1, 'to_replace', 'replacement'
                                               returning_values :strg_out, :strg_replaced;

          select my_table.my_text,
                   (select lib_string_replace.strg_out
                      from lib_string_replace( my_table.my_text, 1, 'MEMO:', 'Memo:'))
            from my_table;

          update my_table
            set my_table.my_text =
                  (select lib_string_replace.strg_out
                     from lib_string_replace( my_table.my_text, 1, 'MEMO:', 'Memo:'));
      */

      if (   (strg_in is null)
          or (strg_beg is null)
          or (strg_beg <= 0)
          or (strg_search is null)
          or (strg_replace is null))
        then
          begin
            strg_out = null;
            strg_replaced = null;
          end
        else
          begin
            strg_replaced = 0;
            execute procedure lib_string_len :strg_in
                                             returning_values :strg_len_orig;
            execute procedure lib_string_len :strg_search
                                             returning_values :strg_len_srch;
            execute procedure lib_string_len :strg_replace
                                             returning_values :strg_len_repl;

            while (strg_len_srch + strg_beg - 1 <= strg_len_orig)
              do
                begin
                  execute procedure lib_string_len :strg_in returning_values :strg_len_orig;

                  execute procedure lib_string_sub :strg_in, :strg_beg, strg_len_srch
                                                   returning_values :strg_comp;

                  if (strg_comp || '.' = strg_search || '.')
                    then
                      begin
                        execute procedure lib_string_sub :strg_in, 1, :strg_beg - 1
                                                         returning_values :strg_pre;
                        execute procedure lib_string_sub :strg_in, :strg_beg + :strg_len_srch,
                                                         :strg_len_orig - (:strg_beg + :strg_len_srch) + 1
                                                         returning_values :strg_pst;
                        strg_in = strg_pre || strg_replace || strg_pst;

                        strg_beg = strg_beg + strg_len_repl - strg_len_srch;

                        strg_replaced = strg_replaced + 1;
                      end

                  strg_beg = strg_beg + 1;
                end

            strg_out = strg_in;
          end

      suspend;
    end