Firebird Create function TRANSLATE as in Oracle

The main problem is that you have declared position1 as a VARCHAR(100) instead of as an INTEGER. This leads to the unhelpful error here as there is no substring version that takes a varchar parameter (and in this context conversion from varchar to integer is not supported).

A working (or at least: compiling) version of your function is:

CREATE OR ALTER FUNCTION TRANSLATE_func   (text varchar(10000),     toReplace varchar(10000),     replacePattern varchar(10000))RETURNS VARCHAR(100)AS   declare variable resultat varchar(100);   declare variable cut varchar(100);   declare variable i integer;   declare variable position1  integer;   declare variable letter  varchar(100);   declare variable lenght integer;BEGIN   i = 1;   resultat ='';   lenght =  char_length(text);   while(i <= lenght) do   begin       cut = substring(text from i for 1);       position1 = position(cut, toReplace);       if (position1 > 0) then       begin           letter = substring(replacePattern from position1 for 1);           resultat = resultat||''||letter;       end       else        begin           resultat = resultat ||''|| cut;       end       i = i+1;   end   return resultat;END

For Firebird 3

SET TERM ^ ;create function translator (    inp varchar(10000),    pat varchar(1000),    rep varchar(1000))returns varchar(10000)as    declare variable tex varchar(10000);    declare variable inp_idx integer = 1;    declare variable cha char(1);    declare variable pos integer;begin  tex = '';  while (inp_idx <= char_length(inp)) do  begin    cha = substring(inp from inp_idx for 1);    pos = position(cha, pat);    if (pos > 0) then      cha = substring(rep from pos for 1);    tex = tex || cha;    inp_idx = inp_idx + 1;  end  return tex;end^SET TERM ; ^

Test

select translator('džiná lasaí ireo dana kýrne číraž', 'ážíýč', 'AZIYC')  from rdb$database;

Result

dZinA lasaI ireo dana kYrne CIraZ