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

지원하지 않는 함수는 만들어서 사용해야 합니다.

출처 : http://www.danner-net.de/fb.htm


^^ 님이 쓰신 글 :
: 오라클에선
:
: SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;
:
: 이런식으로 'J' 문자를 'BL'로 변경할 수 있는데...
:
: 불새에선 REPLACE를 쓸 수 없네요..
:
: 문자열을 치환 할 수 있는 함수 아시는분 알려주세요~~