Firebird Create function TRANSLATE as in Oracle

Question

I try to create a function with the same functionality as the TRANSLATE function in Oracle, I have created function in Oracle and it works good but I have a problem when I try rewrite this code to Firebird. I get an error that 'expression evaluation not supported'. Why?

CREATE FUNCTION TRANSLATEE
(text varchar(10000),
toReplace varchar(10000),
replacePattern varchar(10000)
)
RETURNs VARCHAR(100)
aS

declare variable cut varchar(100);
declare variable i integer;
declare variable position1  varchar(100);
declare variable resultat  varchar(100);
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(:toReplace , cut);
      if (position(:toReplace , cut) >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;

Are you using Firebird 3 (beta)? Earlier versions of Firebird don't have procedural functions (only functions that are defined in external libraries, and stored procedures). — Mark Rotteveel, Apr 11 '15 at 09:24

Mark Rotteveel · Answer 1 · 2016-01-25T13:10:19.830

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

score 0 · Answer 2 · answered Dec 03 '19 at 10:42

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

Firebird Create function TRANSLATE as in Oracle

2 Answers2