New Built-in Functions

  select abs(amount) from transactions;
            

  select acos(x) from y;
            

  select ascii_char(x) from y;
            

Returns the ASCII code of the first character of the specified string.

  select ascii_val(x) from y;
            

  select asin(x) from y;
            

  select atan(x) from y;
            

  select atan2(x, y) from z;
            

  select bin_and(flags, 1) from x;
            

  select bin_or(flags1, flags2) from x;
            

  select bin_shl(flags1, 1) from x;
            

  select bin_shr(flags1, 1) from x;
            

  select bin_xor(flags1, flags2) from x;
            

  select
    rdb$relation_name,
    bit_length(rdb$relation_name),
    bit_length(trim(rdb$relation_name))
  from rdb$relations;
              

    1) select ceil(val) from x;
    2) select ceil(2.1), ceil(-2.1) from rdb$database;  -- returns 3, -2
            

  select
    rdb$relation_name,
    char_length(rdb$relation_name),
    char_length(trim(rdb$relation_name))
  from rdb$relations;
              

  select cos(x) from y;
            

  select cosh(x) from y;
            

  select cot(x) from y;
            

DATEADD( <number> <timestamp_part> TO <date_time> )
DATEADD( <timestamp_part>, <number>, <date_time> )
timestamp_part ::= { YEAR | MONTH | DAY | HOUR | 
                     MINUTE | SECOND | MILLISECOND }
              

select dateadd(day, -1, current_date) as yesterday
  from rdb$database;
  
  /* or (expanded syntax) */
select dateadd(-1 day to current_date) as yesterday
  from rdb$database;
            

DATEDIFF( <timestamp_part> FROM <date_time> TO <date_time> )
DATEDIFF( <timestamp_part>, <date_time>, <date_time> )
timestamp_part ::= { YEAR | MONTH | DAY |
                     HOUR | MINUTE | SECOND | MILLISECOND }
              

select datediff(
  DAY, (cast('TOMORROW' as date) -10), current_date) 
    as datediffresult
from rdb$database;
            

Format:

DECODE( <expression>, <search>, <result>
  [ , <search>, <result> ... ] [, <default> ]
            

Example

  select decode(state, 0, 'deleted', 1, 'active', 'unknown') from things;
            

  select exp(x) from y;
            

    1) select floor(val) from x;
    2) select floor(2.1), floor(-2.1)
          from rdb$database;  -- returns 2, -3
            

 insert into records (id) value (gen_uuid());
            

  select hash(x) from y;
            

  select left(name, char_length(name) - 10)
        from people
        where name like '% FERNANDES';
            

  select ln(x) from y;
            

  select log(x, 10) from y;
            

  select log10(x) from y;
            

isql -q -ch dos850

SQL> create database 'test.fdb';
SQL> create table t (c char(1) character set dos850);
SQL> insert into t values ('A');
SQL> insert into t values ('E');
SQL> insert into t values ('Á');;
SQL> insert into t values ('É');
SQL> select c, lower(c) from t;
C      LOWER
====== ======
A      a
E      e
Á      á
É      é
    

  select lpad(x, 10) from y;
            

  select maxvalue(v1, v2, 10) from x;
            

  select minvalue(v1, v2, 10) from x;
            

  select mod(x, 10) from y;
            

  select
    rdb$relation_name,
    octet_length(rdb$relation_name),
    octet_length(trim(rdb$relation_name))
  from rdb$relations;
              

OVERLAY( <string1> PLACING <string2> FROM <start> [ FOR <length> ] )
              

  SUBSTRING(<string1>, 1 FOR <start> - 1) || <string2> ||
  SUBSTRING(<string1>, <start> + <length>)
              

  val = PI();
            

  POSITION(  <string> IN <string>  )
  POSITION( <string>, <string> [, <offset-position>] )
            

  select rdb$relation_name
    from rdb$relations
    where position('RDB$' IN rdb$relation_name) = 1;
/* */
  position ('be', 'To be or not to be', 10)
            

returns 17. The first occurrence of 'be' occurs within the offset and is ignored.

  position ('be', 'To buy or not to buy', 10)
            

returns 0 because the searched substring was not found.

  select power(x, 10) from y;
            

  select * from x order by rand();
            

  select replace(x, ' ', ',') from y;
            

  create index people_email on people
    computed by (reverse(email));
  select * from people
    where reverse(email) starting with reverse('.br');
            

  select right(rdb$relation_name, char_length(rdb$relation_name) - 4)
    from rdb$relations
    where rdb$relation_name like 'RDB$%';
            

  select round(salary * 1.1, 0) from people;
              

  select rpad(x, 10) from y;
            

  select sign(x) from y;
            

  select sin(x) from y;
            

  select sinh(x) from y;
            

  select sqrt(x) from y;
            

  select tan(x) from y;
            

  select tanh(x) from y;
            

TRIM <left paren> [ [ <trim specification> ] [ <trim character> ]
  FROM ] <value expression> <right paren>

   <trim specification> ::=  LEADING  | TRAILING  | BOTH

   <trim character> ::= <value expression>
              

Rules

Example A)

  select
    rdb$relation_name,
    trim(leading 'RDB$' from rdb$relation_name)
  from rdb$relations
    where rdb$relation_name starting with 'RDB$';
              

Example B)

  select
    trim(rdb$relation_name) || ' is a system table'
  from rdb$relations
    where rdb$system_flag = 1;
              

  1) select trunc(x) from y;
  2) select trunc(-2.8), trunc(2.8)
       from rdb$database;  -- returns -2, 2
  3) select trunc(987.65, 1), trunc(987.65, -1)
       from rdb$database;  -- returns 987.60, 980.00