Firebird Documentation IndexFirebird 2.1 Release Notes → New Built-in Functions
Firebird Home Firebird Home Prev: Firebird 2 Project TeamsFirebird Documentation IndexUp: Firebird 2.1 Release NotesNext: International Character Sets

New Built-in Functions

(Firebird 2.1)

Function Format Description
ABS ABS( <number> ) Returns the absolute value of a number.
  select abs(amount) from transactions;
            
ACOS ACOS( <number> ) Returns the arc cosine of a number. Argument to ACOS must be in the range -1 to 1. Returns a value in the range 0 to PI.
  select acos(x) from y;
            
ASCII_CHAR ASCII_CHAR( <number> ) Returns the ASCII character with the specified code. The argument to ASCII_CHAR must be in the range 0 to 255. The result is returned in character set NONE.
  select ascii_char(x) from y;
            
ASCII_VAL ASCII_VAL( <string> )

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

  1. Returns 0 if the string is empty

  2. Throws an error if the first character is multi-byte

  3. The argument may be a text BLOB of 32,767 bytes or less

  select ascii_val(x) from y;
            
ASIN ASIN( <number> ) Returns the arc sine of a number. The argument to ASIN must be in the range -1 to 1. It returns a result in the range -PI/2 to PI/2.
  select asin(x) from y;
            
ATAN ATAN( <number> ) Returns the arc tangent of a number. Returns a value in the range -PI/2 to PI/2.
  select atan(x) from y;
            
ATAN2 ATAN2( <number>, <number> ) Returns the arc tangent of the first number / the second number. Returns a value in the range -PI to PI.
  select atan2(x, y) from z;
            
BIN_AND BIN_AND( <number> [, <number> ...] ) Returns the result of a binary AND operation performed on all arguments.
  select bin_and(flags, 1) from x;
            
BIN_OR BIN_OR( <number> [, <number> ...] ) Returns the result of a binary OR operation performed on all arguments.
  select bin_or(flags1, flags2) from x;
            
BIN_SHL BIN_SHL( <number>, <number> ) Returns the result of a binary shift left operation performed on the arguments (first << second).
  select bin_shl(flags1, 1) from x;
            
BIN_SHR BIN_SHR( <number>, <number> ) Returns the result of a binary shift right operation performed on the arguments (first >> second).
  select bin_shr(flags1, 1) from x;
            
BIN_XOR BIN_XOR( <number> [, <number> ...] ) Returns the result of a binary XOR operation performed on all arguments.
  select bin_xor(flags1, flags2) from x;
            
BIT_LENGTH BIT_LENGTH( <string> | <string_expr> ) Returns the length of a string in bits.
  select
    rdb$relation_name,
    bit_length(rdb$relation_name),
    bit_length(trim(rdb$relation_name))
  from rdb$relations;
              
CEIL | CEILING { CEIL | CEILING }( <number> ) Returns a value representing the smallest integer that is greater than or equal to the input argument.
    1) select ceil(val) from x;
    2) select ceil(2.1), ceil(-2.1) from rdb$database;  -- returns 3, -2
            
CHAR_LENGTH | CHARACTER_LENGTH CHAR_LENGTH( <string> | <string_expr> ) Returns the number of characters in a string or expression result.
  select
    rdb$relation_name,
    char_length(rdb$relation_name),
    char_length(trim(rdb$relation_name))
  from rdb$relations;
              
COS COS( <number> ) Returns the cosine of a number. The angle is specified in radians and returns a value in the range -1 to 1.
  select cos(x) from y;
            
COSH COSH( <number> ) Returns the hyperbolic cosine of a number.
  select cosh(x) from y;
            
COT COT( <number> ) Returns 1 / tan(argument).
  select cot(x) from y;
            
DATEADD See below Returns a date/time/timestamp value increased (or decreased, when negative) by the specified amount of time.
Format:
DATEADD( <number> <timestamp_part> TO <date_time> )
DATEADD( <timestamp_part>, <number>, <date_time> )
timestamp_part ::= { YEAR | MONTH | DAY | HOUR | 
                     MINUTE | SECOND | MILLISECOND }
              


1. YEAR, MONTH and DAY cannot be used with time values.
2. HOUR, MINUTE, SECOND and MILLISECOND cannot be used with date
   values.
3. All timestamp_part values can be used with timestamp values.
              

Example
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 See below Returns an exact numeric value representing the interval of time from the first date/time/timestamp value to the second one.
Format:
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 }
              


1.  Returns a positive value if the second value is greater than
    the first one, negative when the first one is greater, or zero
    when they are equal.
2.  Comparison of date with time values is invalid.
3.  YEAR, MONTH, and DAY cannot be used with time values.
4.  HOUR, MINUTE, SECOND and MILLISECOND cannot be used with date values.
5.  All timestamp_part values can be used with timestamp values.
              

Example
select datediff(
  DAY, (cast('TOMORROW' as date) -10), current_date) 
    as datediffresult
from rdb$database;
            
DECODE See below DECODE is a shortcut for a CASE ... WHEN ... ELSE expression.

Format:

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

Example

  select decode(state, 0, 'deleted', 1, 'active', 'unknown') from things;
            
EXP EXP( <number> ) Returns the exponential e to the argument.
  select exp(x) from y;
            
FLOOR FLOOR( <number> ) Returns a value representing the largest integer that is less than or equal to the input argument.
    1) select floor(val) from x;
    2) select floor(2.1), floor(-2.1)
          from rdb$database;  -- returns 2, -3
            
GEN_UUID GEN_UUID() -- no arguments Returns a universal unique number.
 insert into records (id) value (gen_uuid());
            
HASH HASH( <string> ) Returns a HASH of a string.
  select hash(x) from y;
            
LEFT LEFT( <string>, <number> ) Returns the substring of a specified length that appears at the start of a left-to-right string.
  select left(name, char_length(name) - 10)
        from people
        where name like '% FERNANDES';
            


1. The first position in a string is 1, not 0.
2. If the <number> argument evaluates to a non-integer, banker's rounding is applied.
            

LN LN( <number> ) Returns the natural logarithm of a number.
  select ln(x) from y;
            
LOG LOG( <number>, <number> ) LOG(x, y) returns the logarithm base x of y.
  select log(x, 10) from y;
            
LOG10 LOG10( <number> ) Returns the logarithm base ten of a number.
  select log10(x) from y;
            
LOWER LOWER( <string> ) (v.2.0.x) Returns the input argument converted to all lower-case characters.
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
Á      á
É      é
    
LPAD LPAD( <string>, <number> [, <string> ] ) LPAD(string1, length, string2) prepends string2 to the beginning of string1 until the length of the result string becomes equal to length.


1.  If the second string is omitted the default value is one space.
2.  If the result string would exceed the length, the second string is truncated.
            

Example
  select lpad(x, 10) from y;
            
MAXVALUE MAXVALUE( <value> [, <value> ...] ) Returns the maximum value of a list of values.
  select maxvalue(v1, v2, 10) from x;
            
MINVALUE MINVALUE( <value> [, <value> ... ) Returns the minimun value of a list of values.
  select minvalue(v1, v2, 10) from x;
            
MOD MOD( <number>, <number> ) Modulo: MOD(X, Y) returns the remainder part of the division of X by Y.
  select mod(x, 10) from y;
            
OCTET_LENGTH OCTET_LENGTH( <string> | <string_expr> ) Returns the length of a string or expression result in bytes.
  select
    rdb$relation_name,
    octet_length(rdb$relation_name),
    octet_length(trim(rdb$relation_name))
  from rdb$relations;
              
OVERLAY See below Returns string1 replacing the substring FROM start FOR length by string2.
Format:
OVERLAY( <string1> PLACING <string2> FROM <start> [ FOR <length> ] )
              


The OVERLAY function is equivalent to:
              

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


1. The first position in a string is 1, not 0.
2. If the <start> and/or <length > argument evaluates to a non-integer, banker's 
     rounding is applied.
3. Fails with text BLOBs in a multi-byte character set if greater than 1024 bytes.
            


If <length> is not specified, CHAR_LENGTH( <string2> ) is implied.
              

PI PI() -- no arguments Returns the PI constant (3.1459...).
  val = PI();
            
POSITION See below Returns the start position of the first string inside the second string, relative to the beginning of the outer string. In the second form, an offset position may be supplied so that the function will ignore any matches occuring before the offset position and return the first match following that.
  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.

POWER POWER( <number>, <number> ) POWER(X, Y) returns X to the power of Y.
  select power(x, 10) from y;
            
RAND RAND() -- no argument Returns a random number between 0 and 1.
  select * from x order by rand();
            
REPLACE REPLACE( <stringtosearch>, <findstring>, <replstring> ) Replaces all occurrences of <findstring> in <stringtosearch> with <replstring>.
  select replace(x, ' ', ',') from y;
            
REVERSE REVERSE( <value> ) Returns a string in reverse order. Useful function for creating an expression index that indexes strings from right to left.
  create index people_email on people
    computed by (reverse(email));
  select * from people
    where reverse(email) starting with reverse('.br');
            
RIGHT RIGHT( <string>, <number> ) Returns the substring, of the specified length, from the right-hand end of a string.
  select right(rdb$relation_name, char_length(rdb$relation_name) - 4)
    from rdb$relations
    where rdb$relation_name like 'RDB$%';
            
ROUND ROUND( <number>, [<number>] ) Returns a number rounded to the specified scale.
Example
  select round(salary * 1.1, 0) from people;
              


If the scale (second parameter) is negative or is omitted, the integer part of the 
value is rounded.  E.g., ROUND(123.456, -1) returns 120.000.
              

RPAD RPAD( <string1>, <length> [, <string2> ] ) Appends <string2> to the end of <string1> until the length of the result string becomes equal to <length>.
Example
  select rpad(x, 10) from y;
            


1.  If the second string is omitted the default value is one space.
2.  If the result string would exceed the length, the final application of 
    <string2> will be truncated.
            

SIGN SIGN( <number> ) Returns 1, 0, or -1 depending on whether the input value is positive, zero or negative, respectively.
  select sign(x) from y;
            
SIN SIN( <number> ) Returns the sine of an input number that is expressed in radians.
  select sin(x) from y;
            
SINH SINH( <number> ) Returns the hyperbolic sine of a number.
  select sinh(x) from y;
            
SQRT SQRT( <number> ) Returns the square root of a number.
  select sqrt(x) from y;
            
TAN TAN( <number> ) Returns the tangent of an input number that is expressed in radians.
  select tan(x) from y;
            
TANH TANH( <number> ) Returns the hyperbolic tangent of a number.
  select tanh(x) from y;
            
TRIM See below (V.2.0.x) Trims characters (default: blanks) from the left and/or right of a string.
TRIM <left paren> [ [ <trim specification> ] [ <trim character> ]
  FROM ] <value expression> <right paren>

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

   <trim character> ::= <value expression>
              

Rules

  1. If <trim specification> is not specified, BOTH is assumed.

  2. If <trim character> is not specified, ' ' is assumed.

  3. If <trim specification> and/or <trim character> is specified, FROM should be specified.

  4. If <trim specification> and <trim character> is not specified, FROM should not be specified.

  5. If a text BLOB substring is specified as <value expression>, the value returned must not exceed 32,767 bytes.

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;
              
TRUNC TRUNC( <number> [, <number> ] ) Returns the integral part (up to the specified scale) of a number.
  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
            

Prev: Firebird 2 Project TeamsFirebird Documentation IndexUp: Firebird 2.1 Release NotesNext: International Character Sets
Firebird Documentation IndexFirebird 2.1 Release Notes → New Built-in Functions