Appendix A: New built-in functions

<< Firebird 2 project teams | Firebird 2.1.6 Release Notes | B. International character sets >>

Appendix A: New built-in functions

(Firebird 2.1)

FunctionFormatDescription
ABSABS(<number>)Returns the absolute value of a number.
select abs(amount) from transactions;

ACOSACOS(<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_CHARASCII_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_VALASCII_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;

ASINASIN( <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;

ATANATAN( <number> )Returns the arc tangent of a number. Returns a value in the range -PI/2 to PI/2.
select atan(x) from y;

ATAN2ATAN2( <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_ANDBIN_AND( <number>[, <number> ...] )Returns the result of a binary AND operation performed on all arguments.
select bin_and(flags, 1) from x;

BIN_NOTBIN_NOT( <number>[, <number> ...] )(Added in v.2.5) Returns the result of a bitwise NOT operation performed on its argument..
select bin_not(flags, 1) from x;

BIN_ORBIN_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_SHRBIN_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_XORBIN_XOR( <number>[, <number> ...] )Returns the result of a binary XOR operation performed on all arguments.
select bin_xor(flags1, flags2) from x;

BIT_LENGTHBIT_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_LENGTHCHARACTER_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;

COSCOS( <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;

COSHCOSH( <number> )Returns the hyperbolic cosine of a number.
select cosh(x) from y;

COTCOT( <number> )Returns 1 / tan(argument).
select cot(x) from y;

DATEADDSee belowReturns 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;

DATEDIFFSee belowReturns 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;

DECODESee belowDECODE 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;

EXPEXP( <number> )Returns the exponential e to the argument.
select exp(x) from y;

FLOORFLOOR( <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_UUIDGEN_UUID() -- no argumentsReturns a universal unique number.
insert into records (id) value (gen_uuid());

HASHHASH( <string> )Returns a HASH of a string.
select hash(x) from y;

LEFTLEFT( <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.

LNLN( <number> )Returns the natural logarithm of a number.
select ln(x) from y;

LOGLOG( <number>, <number> )LOG(x, y) returns the logarithm base x of y.
select log(x, 10) from y;

LOG10LOG10( <number> )Returns the logarithm base ten of a number.
select log10(x) from y;

LOWERLOWER( <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
Á______á
É______é

LPADLPAD( <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;

MAXVALUEMAXVALUE( <value> [, <value> ...] )Returns the maximum value of a list of values.
select maxvalue(v1, v2, 10) from x;

MINVALUEMINVALUE( <value> [, <value> ... )Returns the minimun value of a list of values.
select minvalue(v1, v2, 10) from x;

MODMOD( <number>, <number> )Modulo: MOD(X, Y) returns the remainder part of the division of X by Y.
select mod(x, 10) from y;

OCTET_LENGTHOCTET_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;

OVERLAYSee belowReturns 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.

PIPI() -- no argumentsReturns the PI constant (3.14159...).
val = PI();

POSITIONSee belowReturns 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 returns a result relative to that position in the outer string.
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.
POWERPOWER( <number>, <number> )POWER(X, Y) returns X to the power of Y.
select power(x, 10) from y;

RANDRAND() -- no argumentReturns a random number between 0 and 1.
select * from x order by rand();

REPLACEREPLACE( <stringtosearch>, <findstring>, <replstring> )Replaces all occurrences of <findstring> in <stringtosearch> with <replstring>.
select replace(x, ' ', ',') from y;

REVERSEREVERSE( <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');

RIGHTRIGHT( <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$%';

ROUNDROUND( <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.

RPADRPAD( <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.

SIGNSIGN( <number> )Returns 1, 0, or -1 depending on whether the input value is positive, zero or negative, respectively.
select sign(x) from y;

SINSIN( <number> )Returns the sine of an input number that is expressed in radians.
select sin(x) from y;

SINHSINH( <number> )Returns the hyperbolic sine of a number.
select sinh(x) from y;

SQRTSQRT( <number> )Returns the square root of a number.
select sqrt(x) from y;

TANTAN( <number> )Returns the tangent of an input number that is expressed in radians.
select tan(x) from y;

TANHTANH( <number> )Returns the hyperbolic tangent of a number.
select tanh(x) from y;

TRIMSee 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;

TRUNCTRUNC( <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

back to top of page
<< Firebird 2 project teams | Firebird 2.1.6 Release Notes | B. International character sets >>