Scalar Functions

Table of Contents

Functions for Working with Context Variables
Mathematical Functions
Functions for Working with Strings
Date and Time Functions
Type Casting Functions
Functions for Bitwise Operations
Functions for Working with UUID
Functions for Working with Generators (Sequences)
Conditional Functions

Upgraders: PLEASE READ!

A large number of functions that were implemented as external functions (UDFs) in earlier versions of Firebird have been progressively re-implemented as internal (built-in) functions. If some external function of the same name as a built-in one is declared in your database, it will remain there and it will override any internal function of the same name.

To make the internal function available, you need either to DROP the UDF, or to use ALTER EXTERNAL FUNCTION the to change the declared name of the UDF.

Functions for Working with Context Variables

Table of Contents

RDB$GET_CONTEXT()
RDB$SET_CONTEXT()

RDB$GET_CONTEXT()

	Note
	RDB$GET_CONTEXT and its counterpart RDB$SET_CONTEXT are actually predeclared UDFs. They are listed here as internal functions because they are always present—the user doesn't have to do anything to make them available.

Available in: DSQL, PSQL * As a declared UDF it should be available in ESQL

Description: Retrieves the value of a context variable from one of the namespaces SYSTEM, USER_SESSION and USER_TRANSACTION.

Syntax:

RDB$GET_CONTEXT ('<namespace>', '<varname>')

		<namespace>  ::=  SYSTEM | USER_SESSION | USER_TRANSACTION
		<varname>    ::=  A case-sensitive string of max. 80 characters

Table 8.3. RDB$GET_CONTEXT Function Parameters

Parameter	Description
namespace	Namespace
varname	Variable name. Case-sensitive. Maximum length is 80 characters

Result type: VARCHAR(255)

Table 8.4. Context variables in the SYSTEM namespace

DB_NAME	Either the full path to the database or—if connecting via the path is disallowed—its alias.
NETWORK_PROTOCOL	The protocol used for the connection: `'TCPv4'`, `'WNET'`, `'XNET'` or `NULL`.
CLIENT_ADDRESS	For TCPv4, this is the IP address. For XNET, the local process ID. For all other protocols this variable is `NULL`.
CURRENT_USER	Same as global CURRENT_USER variable.
CURRENT_ROLE	Same as global CURRENT_ROLE variable.
SESSION_ID	Same as global CURRENT_CONNECTION variable.
TRANSACTION_ID	Same as global CURRENT_TRANSACTION variable.
ISOLATION_LEVEL	The isolation level of the current transaction: 'READ COMMITTED', 'SNAPSHOT' or 'CONSISTENCY'.
ENGINE_VERSION	The Firebird engine (server) version. Added in 2.1.

Return values and error behaviour: If the polled variable exists in the given namespace, its value will be returned as a string of max. 255 characters. If the namespace doesn't exist or if you try to access a non-existing variable in the SYSTEM namespace, an error is raised. If you poll a non-existing variable in one of the other namespaces, NULL is returned. Both namespace and variable names must be given as single-quoted, case-sensitive, non-NULL strings.

Examples:

select rdb$get_context('SYSTEM', 'DB_NAME') from rdb$database

New.UserAddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS');

insert into MyTable (TestField)
		values (rdb$get_context('USER_SESSION', 'MyVar'))

RDB$SET_CONTEXT()

	Note
	RDB$SET_CONTEXT and its counterpart RDB$GET_CONTEXT are actually predeclared UDFs. They are listed here as internal functions because they are always present—the user doesn't have to do anything to make them available.

Available in: DSQL, PSQL * As a declared UDF it should be available in ESQL

Description: Creates, sets or unsets a variable in one of the user-writable namespaces USER_SESSION and USER_TRANSACTION.

Syntax:

RDB$SET_CONTEXT ('<namespace>', '<varname>', <value> | NULL)

		<namespace>  ::=  USER_SESSION | USER_TRANSACTION
		<varname>    ::=  A case-sensitive string of max. 80 characters
		<value>      ::=  A value of any type, as long as it's castable
		              to a VARCHAR(255)

Table 8.5. RDB$SET_CONTEXT Function Parameters

Parameter	Description
namespace	Namespace
varname	Variable name. Case-sensitive. Maximum length is 80 characters
value	Data of any type provided it can be cast to VARCHAR(255)

Result type: INTEGER

The namespaces: The USER_SESSION and USER_TRANSACTION namespaces are initially empty. The user can create and set variables in them with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT(). The USER_SESSION context is bound to the current connection. Variables in USER_TRANSACTION only exist in the transaction in which they have been set. When the transaction ends, the context and all the variables defined in it are destroyed.

Return values and error behaviour: The function returns 1 if the variable already existed before the call and 0 if it didn't. To remove a variable from a context, set it to NULL. If the given namespace doesn't exist, an error is raised. Both namespace and variable names must be entered as single-quoted, case-sensitive, non-NULL strings.

Examples:

select rdb$set_context('USER_SESSION', 'MyVar', 493) from rdb$database

rdb$set_context('USER_SESSION', 'RecordsFound', RecCounter);

select rdb$set_context('USER_TRANSACTION', 'Savepoints', 'Yes')
		from rdb$database

Notes:

The maximum number of variables in any single context is 1000.
All USER_TRANSACTION variables will survive a ROLLBACK RETAIN (see ROLLBACK Options) or ROLLBACK TO SAVEPOINT unaltered, no matter at which point during the transaction they were set.
Due to its UDF-like nature, RDB$SET_CONTEXT can—in PSQL only—be called like a void function, without assigning the result, as in the second example above. Regular internal functions don't allow this type of use.

Mathematical Functions

Table of Contents

ABS()
ACOS()
ASIN()
ATAN()
ATAN2()
CEIL(), CEILING()
COS()
COSH()
COT()
EXP()
FLOOR()
LN()
LOG()
LOG10()
MOD()
PI()
POWER()
RAND()
ROUND()
SIGN()
SIN()
SINH()
SQRT()
TAN()
TANH()
TRUNC()

ABS()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ABS (number)

Table 8.6. ABS Function Parameter

Parameter	Description
value	An expression of a numeric type

Result type: Numerical

Description: Returns the absolute value of the argument.

ACOS()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ACOS (number)

Table 8.7. ACOS Function Parameter

Parameter	Description
value	An expression of a numeric type within the range [-1; 1]

Result type: DOUBLE PRECISION

Description: Returns the arc cosine of the argument.

The result is an angle in the range [0, pi].
If the argument is outside the range [-1, 1], NaN is returned.

ASIN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ASIN (number)

Table 8.8. ASIN Function Parameter

Parameter	Description
value	An expression of a numeric type within the range [-1; 1]

Result type: DOUBLE PRECISION

Description: Returns the arc sine of the argument.

The result is an angle in the range [-pi/2, pi/2].
If the argument is outside the range [-1, 1], NaN is returned.

ATAN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ATAN (number)

Table 8.9. ATAN Function Parameter

Parameter	Description
value	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: The function ATAN returns the arc tangent of the argument. The result is an angle in the range <-pi/2, pi/2>.

ATAN2()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ATAN2 (y, x)

Table 8.10. ATAN2 Function Parameters

Parameter	Description
x	An expression of a numeric type
y	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the angle whose sine-to-cosine ratio is given by the two arguments, and whose sine and cosine signs correspond to the signs of the arguments. This allows results across the entire circle, including the angles -pi/2 and pi/2.

The result is an angle in the range [-pi, pi].
If x is negative, the result is pi if y is 0, and -pi if y is -0.
If both y and x are 0, the result is meaningless. Starting with Firebird 3, an error will be raised if both arguments are 0. At v.2.5.4, it is still not fixed in lower versions. For more details, visit Tracker ticket CORE-3201.

Notes:

A fully equivalent description of this function is the following: ATAN2(y, x) is the angle between the positive X-axis and the line from the origin to the point (x, y). This also makes it obvious that ATAN2(0, 0) is undefined.
If x is greater than 0, ATAN2(y, x) is the same as ATAN(y/x).
If both sine and cosine of the angle are already known, ATAN2(sin, cos) gives the angle.

CEIL(), CEILING()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details (Affects CEILING only)

Syntax:

CEIL[ING] (number)

Table 8.11. CEIL[ING] Function Parameters

Parameter	Description
number	An expression of a numeric type

Result type: BIGINT or DOUBLE PRECISION

Description: Returns the smallest whole number greater than or equal to the argument.

COS()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

COS (angle)

Table 8.12. COS Function Parameter

Parameter	Description
angle	An angle in radians

Result type: DOUBLE PRECISION

Description: Returns an angle's cosine. The argument must be given in radians.

Any non-NULL result is—obviously—in the range [-1, 1].

COSH()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

COSH (number)

Table 8.13. COSH Function Parameter

Parameter	Description
number	A number of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the hyperbolic cosine of the argument.

Any non-NULL result is in the range [1, INF].

COT()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

COT (angle)

Table 8.14. COT Function Parameter

Parameter	Description
angle	An angle in radians

Result type: DOUBLE PRECISION

Description: Returns an angle's cotangent. The argument must be given in radians.

EXP()

Available in: DSQL, PSQL

Syntax:

EXP (number)

Table 8.15. EXP Function Parameter

Parameter	Description
number	A number of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the natural exponential, e^number

FLOOR()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

FLOOR (number)

Table 8.16. FLOOR Function Parameter

Parameter	Description
number	An expression of a numeric type

Result type: BIGINT or DOUBLE PRECISION

Description: Returns the largest whole number smaller than or equal to the argument.

LN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

LN (number)

Table 8.17. LN Function Parameter

Parameter	Description
number	An expression of a numeric type

Description: Returns the natural logarithm of the argument.

An error is raised if the argument is negative or 0.

Result type: DOUBLE PRECISION

LOG()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

LOG (x, y)

Table 8.18. LOG Function Parameters

Parameter	Description
x	Base. An expression of a numeric type
y	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the x-based logarithm of y.

If either argument is 0 or below, an error is raised. (Before 2.5, this would result in NaN, ±INF or 0, depending on the exact values of the arguments.)
If both arguments are 1, NaN is returned.
If x = 1 and y < 1, -INF is returned.
If x = 1 and y > 1, INF is returned.

LOG10()

Available in: DSQL, PSQL

Changed in: 2.5

Possible name conflict: YES—>Read details

Syntax:

LOG10 (number)

Table 8.19. LOG10 Function Parameter

Parameter	Description
number	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the 10-based logarithm of the argument.

An error is raised if the argument is negative or 0. (In versions prior to 2.5, such values would result in NaN and -INF, respectively.)

MOD()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

MOD (a, b)

Table 8.20. MOD Function Parameters

Parameter	Description
a	An expression of a numeric type
b	An expression of a numeric type

Result type: INTEGER or BIGINT

Description: Returns the remainder of an integer division.

Non-integer arguments are rounded before the division takes place. So, “7.5 mod 2.5” gives 2 (8 mod 3), not 0.

PI()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

PI ()

Result type: DOUBLE PRECISION

Description: Returns an approximation of the value of pi.

POWER()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

POWER (x, y)

Table 8.21. POWER Function Parameters

Parameter	Description
x	An expression of a numeric type
y	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns x to the power of y.

If x negative, an error is raised.

RAND()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

RAND ()

Result type: DOUBLE PRECISION

Description: Returns a random number between 0 and 1.

ROUND()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ROUND (<number> [, <scale>])

Table 8.22. ROUND Function Parameters

Parameter

Description

number

An expression of a numeric type

scale

An integer specifying the number of decimal places toward which rounding is to be performed, e.g.:

2 for rounding to the nearest multiple of 0.01

1 for rounding to the nearest multiple of 0.1

0 for rounding to the nearest whole number

-1 for rounding to the nearest multiple of 10

-2 for rounding to the nearest multiple of 100

Result type: INTEGER, (scaled) BIGINT or DOUBLE PRECISION

Description: Rounds a number to the nearest integer. If the fractional part is exactly 0.5, rounding is upward for positive numbers and downward for negative numbers. With the optional scale argument, the number can be rounded to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.

	Important
	If you are used to the behaviour of the external function `ROUND`, please notice that the internal function always rounds halves away from zero, i.e. downward for negative numbers.

Examples: If the scale argument is present, the result usually has the same scale as the first argument:

ROUND(123.654, 1) -- returns 123.700 (not 123.7)
ROUND(8341.7, -3) -- returns 8000.0 (not 8000)
ROUND(45.1212, 0) -- returns 45.0000 (not 45)

Otherwise, the result scale is 0:

ROUND(45.1212) -- returns 45

SIGN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

SIGN (number)

Table 8.23. SIGN Function Parameter

Parameter	Description
number	An expression of a numeric type

Result type: SMALLINT

Description: Returns the sign of the argument: -1, 0 or 1.

SIN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

SIN (angle)

Table 8.24. SIN Function Parameter

Parameter	Description
angle	An angle, in radians

Result type: DOUBLE PRECISION

Description: Returns an angle's sine. The argument must be given in radians.

Any non-NULL result is—obviously—in the range [-1, 1].

SINH()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

SINH (number)

Table 8.25. SINH Function Parameter

Parameter	Description
number	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the hyperbolic sine of the argument.

SQRT()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

SQRT (number)

Table 8.26. SQRT Function Parameter

Parameter	Description
number	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the square root of the argument.

TAN()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

TAN (angle)

Table 8.27. TAN Function Parameter

Parameter	Description
angle	An angle, in radians

Result type: DOUBLE PRECISION

Description: Returns an angle's tangent. The argument must be given in radians.

TANH()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

TANH (number)

Table 8.28. TANH Function Parameters

Parameter	Description
number	An expression of a numeric type

Result type: DOUBLE PRECISION

Description: Returns the hyperbolic tangent of the argument.

Due to rounding, any non-NULL result is in the range [-1, 1] (mathematically, it's <-1, 1>).

TRUNC()

Available in: DSQL, PSQL

Syntax:

TRUNC (<number> [, <scale>])

Table 8.29. TRUNC Function Parameters

Parameter

Description

number

An expression of a numeric type

An integer specifying the number of decimal places toward which truncating is to be performed, e.g.:

2 for truncating to the nearest multiple of 0.01

1 for truncating to the nearest multiple of 0.1

0 for truncating to the nearest whole number

-1 for truncating to the nearest multiple of 10

-2 for truncating to the nearest multiple of 100

Result type: INTEGER, (scaled) BIGINT or DOUBLE PRECISION

Description: Returns the integer part of a number. With the optional scale argument, the number can be truncated to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.

Notes:

If the scale argument is present, the result usually has the same scale as the first argument, e.g.
- TRUNC(789.2225, 2) returns 789.2200 (not 789.22)
- TRUNC(345.4, -2) returns 300.0 (not 300)
- TRUNC(-163.41, 0) returns -163.00 (not -163)
Otherwise, the result scale is 0:
- TRUNC(-163.41) returns -163

	Important
	If you are used to the behaviour of the external function `TRUNCATE`, please notice that the internal function TRUNC always truncates toward zero, i.e. upward for negative numbers.

Functions for Working with Strings

Table of Contents

ASCII_CHAR()
ASCII_VAL()
BIT_LENGTH()
CHAR_LENGTH(), CHARACTER_LENGTH()
HASH()
LEFT()
LOWER()
LPAD()
OCTET_LENGTH()
OVERLAY()
POSITION()
REPLACE()
REVERSE()
RIGHT()
RPAD()
SUBSTRING()
TRIM()
UPPER()

ASCII_CHAR()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ASCII_CHAR (<code>)

Table 8.30. ASCII_CHAR Function Parameter

Parameter	Description
code	An integer within the range from 0 to 255

Result type: [VAR]CHAR(1) CHARACTER SET NONE

Description: Returns the ASCII character corresponding to the number passed in the argument.

	Important
	If you are used to the behaviour of the `ASCII_CHAR` UDF, which returns an empty string if the argument is 0, please notice that the internal function correctly returns a character with ASCII code 0 here.

ASCII_VAL()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

ASCII_VAL (ch)

Table 8.31. ASCII_VAL Function Parameter

Parameter	Description
ch	A string of the [VAR]CHAR data type or a text BLOB with the maximum size of 32,767 bytes

Result type: SMALLINT

Description: Returns the ASCII code of the character passed in.

If the argument is a string with more than one character, the ASCII code of the first character is returned.
If the argument is an empty string, 0 is returned.
If the argument is NULL, NULL is returned.
If the first character of the argument string is multi-byte, an error is raised. (A bug in Firebird 2.1—2.1.3 and 2.5 causes an error to be raised if any character in the string is multi-byte. This is fixed in versions 2.1.4 and 2.5.1.)

BIT_LENGTH()

Available in: DSQL, PSQL

Syntax:

BIT_LENGTH (string)

Table 8.32. BIT_LENGTH Function Parameter

Parameter	Description
string	An expression of a string type

Result type: INTEGER

Description: Gives the length in bits of the input string. For multi-byte character sets, this may be less than the number of characters times 8 times the “formal” number of bytes per character as found in RDB$CHARACTER_SETS.

	Note
	With arguments of type CHAR, this function takes the entire formal string length (e.g. the declared length of a field or variable) into account. If you want to obtain the “logical” bit length, not counting the trailing spaces, right-TRIM the argument before passing it to BIT_LENGTH.

BLOB support: Since Firebird 2.1, this function fully supports text BLOBs of any length and character set.

Examples:

select bit_length('Hello!') from rdb$database
		-- returns 48

select bit_length(_iso8859_1 'Grüß di!') from rdb$database
		-- returns 64: ü and ß take up one byte each in ISO8859_1

select bit_length
		(cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
		from rdb$database
		-- returns 80: ü and ß take up two bytes each in UTF8

select bit_length
		(cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
		from rdb$database
		-- returns 208: all 24 CHAR positions count, and two of them are 16-bit

CHAR_LENGTH(), CHARACTER_LENGTH()

Available in: DSQL, PSQL

Syntax:

CHAR_LENGTH (str)
		CHARACTER_LENGTH (string)

Table 8.33. CHAR[ACTER]_LENGTH Function Parameter

Parameter	Description
string	An expression of a string type

Result type: INTEGER

Description: Gives the length in characters of the input string.

	Notes
	With arguments of type CHAR, this function returns the formal string length (i.e. the declared length of a field or variable). If you want to obtain the “logical” length, not counting the trailing spaces, right-TRIM the argument before passing it to CHAR[ACTER]_LENGTH. >BLOB support: Since Firebird 2.1, this function fully supports text BLOBs of any length and character set.

Examples:

select char_length('Hello!') from rdb$database
		-- returns 6

select char_length(_iso8859_1 'Grüß di!') from rdb$database
		-- returns 8

select char_length
		(cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
		from rdb$database
		-- returns 8; the fact that ü and ß take up two bytes each is irrelevant

select char_length
		(cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
		from rdb$database
		-- returns 24: all 24 CHAR positions count

HASH()

Available in: DSQL, PSQL

Syntax:

HASH (string)

Table 8.34. HASH Function Parameter

Parameter	Description
string	An expression of a string type

Description: Returns a hash value for the input string. This function fully supports text BLOBs of any length and character set.

Result type: BIGINT

LEFT()

Available in: DSQL, PSQL

Syntax:

LEFT (string, length)

Table 8.35. LEFT Function Parameters

Parameter	Description
string	An expression of a string type
number	Integer. Defines the number of characters to return

Result type: VARCHAR or BLOB

Description: Returns the leftmost part of the argument string. The number of characters is given in the second argument.

This function fully supports text BLOBs of any length, including those with a multi-byte character set.
If string is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n the length of the input string.
If the length argument exceeds the string length, the input string is returned unchanged.
If the length argument is not a whole number, bankers' rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

LOWER()

Available in: DSQL, ESQL, PSQL

Possible name conflict: YES—>Read details below

Syntax:

LOWER (string)

Table 8.36. LOWER Function ParameterS

Parameter	Description
string	An expression of a string type

Result type: (VAR)CHAR or BLOB

Description: Returns the lower-case equivalent of the input string. The exact result depends on the character set. With ASCII or NONE for instance, only ASCII characters are lowercased; with OCTETS, the entire string is returned unchanged. Since Firebird 2.1 this function also fully supports text BLOBs of any length and character set.

	Name Clash
	Because LOWER is a reserved word, the internal function will take precedence even if the external function by that name has also been declared. To call the (inferior!) external function, use double-quotes and the exact capitalisation, as in `"LOWER"`(`str`).

Example:

select Sheriff from Towns
		where lower(Name) = 'cooper''s valley'

LPAD()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

LPAD (str, endlen [, padstr])

Table 8.37. LPAD Function Parameters

Parameter	Description
str	An expression of a string type
endlen	Output string length
padstr	The character or string to be used to pad the source string up to the specified length. Default is space (' ')

Result type: VARCHAR or BLOB

Description: Left-pads a string with spaces or with a user-supplied string until a given length is reached.

This function fully supports text BLOBs of any length and character set.
If str is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(endlen).
If padstr is given and equals '' (empty string), no padding takes place.
If endlen is less than the current string length, the string is truncated to endlen, even if padstr is the empty string.

	Note
	In Firebird 2.1—2.1.3, all non-BLOB results were of type VARCHAR(32765), which made it advisable to cast them to a more modest size. This is no longer the case.

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. Although it does try to limit memory consumption, this may affect performance if huge BLOBs are involved.

Examples:

  lpad ('Hello', 12)                 -- returns '       Hello'
		lpad ('Hello', 12, '-')            -- returns '-------Hello'
		lpad ('Hello', 12, '')             -- returns 'Hello'
		lpad ('Hello', 12, 'abc')          -- returns 'abcabcaHello'
		lpad ('Hello', 12, 'abcdefghij')   -- returns 'abcdefgHello'
		lpad ('Hello', 2)                  -- returns 'He'
		lpad ('Hello', 2, '-')             -- returns 'He'
		lpad ('Hello', 2, '')              -- returns 'He'

OCTET_LENGTH()

Available in: DSQL, PSQL

Syntax:

OCTET_LENGTH (string)

Table 8.38. OCTET_LENGTH Function Parameter

Parameter	Description
string	An expression of a string type

Result type: INTEGER

Description: Gives the length in bytes (octets) of the input string. For multi-byte character sets, this may be less than the number of characters times the “formal” number of bytes per character as found in RDB$CHARACTER_SETS.

	Note
	With arguments of type CHAR, this function takes the entire formal string length (e.g. the declared length of a field or variable) into account. If you want to obtain the “logical” byte length, not counting the trailing spaces, right-TRIM the argument before passing it to OCTET_LENGTH.

BLOB support: Since Firebird 2.1, this function fully supports text BLOBs of any length and character set.

Examples:

select octet_length('Hello!') from rdb$database
		-- returns 6

select octet_length(_iso8859_1 'Grüß di!') from rdb$database
		-- returns 8: ü and ß take up one byte each in ISO8859_1

select octet_length
		(cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
		from rdb$database
		-- returns 10: ü and ß take up two bytes each in UTF8

select octet_length
		(cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
		from rdb$database
		-- returns 26: all 24 CHAR positions count, and two of them are 2-byte

OVERLAY()

Available in: DSQL, PSQL

Syntax:

OVERLAY (string PLACING replacement FROM pos [FOR length])

Table 8.39. OVERLAY Function Parameters

Parameter	Description
string	The string into which the replacement takes place
replacement	Replacement string
pos	The position from which replacement takes place (starting position)
length	The number of characters that are to be overwritten

Result type: VARCHAR or BLOB

Description: OVERLAY() overwrites part of a string with another string. By default, the number of characters removed from (overwritten in) the host string equals the length of the replacement string. With the optional fourth argument, a different number of characters can be specified for removal.

This function supports BLOBs of any length.
If string or replacement is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n the sum of the lengths of string and replacement.
As usual in SQL string functions, pos is 1-based.
If pos is beyond the end of string, replacement is placed directly after string.
If the number of characters from pos to the end of string is smaller than the length of replacement (or than the length argument, if present), string is truncated at pos and replacement placed after it.
The effect of a “FOR 0” clause is that replacement is simply inserted into string.
If any argument is NULL, the result is NULL.
If pos or length is not a whole number, bankers' rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

Examples:

  overlay ('Goodbye' placing 'Hello' from 2)    -- returns 'GHelloe'
		overlay ('Goodbye' placing 'Hello' from 5)    -- returns 'GoodHello'
		overlay ('Goodbye' placing 'Hello' from 8)    -- returns 'GoodbyeHello'
		overlay ('Goodbye' placing 'Hello' from 20)   -- returns 'GoodbyeHello'

		overlay ('Goodbye' placing 'Hello' from 2 for 0)   -- r. 'GHellooodbye'
		overlay ('Goodbye' placing 'Hello' from 2 for 3)   -- r. 'GHellobye'
		overlay ('Goodbye' placing 'Hello' from 2 for 6)   -- r. 'GHello'
		overlay ('Goodbye' placing 'Hello' from 2 for 9)   -- r. 'GHello'

		overlay ('Goodbye' placing '' from 4)         -- returns 'Goodbye'
		overlay ('Goodbye' placing '' from 4 for 3)   -- returns 'Gooe'
		overlay ('Goodbye' placing '' from 4 for 20)  -- returns 'Goo'

		overlay ('' placing 'Hello' from 4)           -- returns 'Hello'
		overlay ('' placing 'Hello' from 4 for 0)     -- returns 'Hello'
		overlay ('' placing 'Hello' from 4 for 20)    -- returns 'Hello'

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

POSITION()

Available in: DSQL, PSQL

Syntax:

POSITION (substr IN string)
| POSITION (substr, string [, startpos])

Table 8.40. POSITION Function Parameters

Parameter	Description
substr	The substring whose position is to be searched for
string	The string which is to be searched
startpos	The position in `string` where the search is to start

Result type: INTEGER

Description: Returns the (1-based) position of the first occurrence of a substring in a host string. With the optional third argument, the search starts at a given offset, disregarding any matches that may occur earlier in the string. If no match is found, the result is 0.

Notes:

The optional third argument is only supported in the second syntax (comma syntax).
The empty string is considered a substring of every string. Therefore, if substr is '' (empty string) and string is not NULL, the result is:
- 1 if startpos is not given;
- startpos if startpos lies within string;
- 0 if startpos lies beyond the end of string.
Notice: A bug in Firebird 2.1—2.1.3 and 2.5 causes POSITION to always return 1 if substr is the empty string. This is fixed in 2.1.4 and 2.5.1.
This function fully supports text BLOBs of any size and character set.

Examples:

  position ('be' in 'To be or not to be')      -- returns 4
		position ('be', 'To be or not to be')        -- returns 4
		position ('be', 'To be or not to be', 4)     -- returns 4
		position ('be', 'To be or not to be', 8)     -- returns 17
		position ('be', 'To be or not to be', 18)    -- returns 0
		position ('be' in 'Alas, poor Yorick!')      -- returns 0

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

REPLACE()

Available in: DSQL, PSQL

Syntax:

REPLACE (str, find, repl)

Table 8.41. REPLACE Function Parameters

Parameter	Description
str	The string in which the replacement is to take place
find	The string to search for
repl	The replacement string

Result type: VARCHAR or BLOB

Description: Replaces all occurrences of a substring in a string.

This function fully supports text BLOBs of any length and character set.
If any argument is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n calculated from the lengths of str, find and repl in such a way that even the maximum possible number of replacements won't overflow the field.
If find is the empty string, str is returned unchanged.
If repl is the empty string, all occurrences of find are deleted from str.
If any argument is NULL, the result is always NULL, even if nothing would have been replaced.

Examples:

  replace ('Billy Wilder',  'il', 'oog')     -- returns 'Boogly Woogder'
		replace ('Billy Wilder',  'il',    '')     -- returns 'Bly Wder'
		replace ('Billy Wilder',  null, 'oog')     -- returns NULL
		replace ('Billy Wilder',  'il',  null)     -- returns NULL
		replace ('Billy Wilder', 'xyz',  null)     -- returns NULL (!)
		replace ('Billy Wilder', 'xyz', 'abc')     -- returns 'Billy Wilder'
		replace ('Billy Wilder',    '', 'abc')     -- returns 'Billy Wilder'

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

REVERSE()

Available in: DSQL, PSQL

Syntax:

REVERSE (str)

Table 8.42. REVERSE Function Parameter

Parameter	Description
string	An expression of a string type

Result type: VARCHAR

Description: Returns a string backwards.

Examples:

  reverse ('spoonful')                 -- returns 'lufnoops'
		reverse ('Was it a cat I saw?')      -- returns '?was I tac a ti saW'

Tip

This function comes in very handy if you want to group, search or order on string endings, e.g. when dealing with domain names or email addresses:

create index ix_people_email on people
  computed by (reverse(email));

select * from people
  where reverse(email) starting with reverse('.br');

RIGHT()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

RIGHT (string, length)

Table 8.43. RIGHT Function Parameters

Parameter	Description
string	An expression of a string type
length	Integer. Defines the number of characters to return

Result type: VARCHAR or BLOB

Description: Returns the rightmost part of the argument string. The number of characters is given in the second argument.

This function supports text BLOBs of any length, but has a bug in versions 2.1—2.1.3 and 2.5 that makes it fail with text BLOBs larger than 1024 bytes that have a multi-byte character set. This has been fixed in versions 2.1.4 and 2.5.1.
If string is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n the length of the input string.
If the length argument exceeds the string length, the input string is returned unchanged.
If the length argument is not a whole number, bankers' rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

RPAD()

Available in: DSQL, PSQL

Changed in: 2.5 (backported to 2.1.4)

Possible name conflict: YES—>Read details

Syntax:

RPAD (str, endlen [, padstr])

Table 8.44. RPAD Function Parameters

Parameter	Description
str	An expression of a string type
endlen	Output string length
endlen	The character or string to be used to pad the source string up to the specified length. Default is space (' ')

Result type: VARCHAR or BLOB

Description: Right-pads a string with spaces or with a user-supplied string until a given length is reached.

This function fully supports text BLOBs of any length and character set.
If str is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(endlen).
If padstr is given and equals '' (empty string), no padding takes place.
If endlen is less than the current string length, the string is truncated to endlen, even if padstr is the empty string.

	Note
	In Firebird 2.1—2.1.3, all non-BLOB results were of type VARCHAR(32765), which made it advisable to cast them to a more modest size. This is no longer the case.

Examples:

  rpad ('Hello', 12)                 -- returns 'Hello       '
		rpad ('Hello', 12, '-')            -- returns 'Hello-------'
		rpad ('Hello', 12, '')             -- returns 'Hello'
		rpad ('Hello', 12, 'abc')          -- returns 'Helloabcabca'
		rpad ('Hello', 12, 'abcdefghij')   -- returns 'Helloabcdefg'
		rpad ('Hello', 2)                  -- returns 'He'
		rpad ('Hello', 2, '-')             -- returns 'He'
		rpad ('Hello', 2, '')              -- returns 'He'

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. Although it does try to limit memory consumption, this may affect performance if huge BLOBs are involved.

SUBSTRING()

Available in: DSQL, PSQL

Changed in: 2.5.1

Syntax:

SUBSTRING (str FROM startpos [FOR length])

Table 8.45. SUBSTRING Function Parameters

Parameter	Description
str	An expression of a string type
startpos	Integer expression, the position from which to start retrieving the substring
length	The number of characters to retrieve after the <startpos>

Result types: VARCHAR(n) or BLOB

Description: Returns a string's substring starting at the given position, either to the end of the string or with a given length.

This function returns the substring starting at character position startpos (the first position being 1). Without the FOR argument, it returns all the remaining characters in the string. With FOR, it returns length characters or the remainder of the string, whichever is shorter.

In Firebird 1.x, startpos and length must be integer literals. In 2.0 and above they can be any valid integer expression.

Starting with Firebird 2.1, this function fully supports binary and text BLOBs of any length and character set. If str is a BLOB, the result is also a BLOB. For any other argument type, the result is a VARCHAR(n). Previously, the result type used to be CHAR(n) if the argument was a CHAR(n) or a string literal.

For non-BLOB arguments, the width of the result field is always equal to the length of str, regardless of startpos and length. So, substring('pinhead' from 4 for 2) will return a VARCHAR(7) containing the string 'he'.

If any argument is NULL, the result is NULL.

Bugs

If str is a BLOB and the length argument is not present, the output is limited to 32767 characters. Workaround: with long BLOBs, always specify char_length(str)—or a sufficiently high integer—as the third argument, unless you are sure that the requested substring fits within 32767 characters.

This bug has been fixed in version 2.5.1; the fix was also backported to 2.1.5.
An older bug in Firebird 2.0, which caused the function to return “false emptystrings” if startpos or length was NULL, was fixed.

Example:

insert into AbbrNames(AbbrName)
		select substring(LongName from 1 for 3) from LongNames

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. Although it does try to limit memory consumption, this may affect performance if huge BLOBs are involved.

See also: POSITION, LEFT, RIGHT, CHAR[ACTER]_LENGTH

TRIM()

Available in: DSQL, PSQL

Syntax:

TRIM ([<adjust>] str)

<adjust> ::=  {[<where>] [what]} FROM

<where> ::=  BOTH | LEADING | TRAILING

Table 8.46. TRIM Function Parameters

Parameter	Description
str	An expression of a string type
where	The position the substring is to be removed from—BOTH \| LEADING \| TRAILING. BOTH is the default
what	The substring that should be removed (multiple times if there are several matches) from the beginning \| the end \| both sides of the input string <str>. By default it is space (' ')

Result type: VARCHAR(n) or BLOB

Description: Removes leading and/or trailing spaces (or optionally other strings) from the input string. Since Firebird 2.1 this function fully supports text BLOBs of any length and character set.

Examples:

select trim ('  Waste no space   ') from rdb$database
		-- returns 'Waste no space'

select trim (leading from '  Waste no space   ') from rdb$database
		-- returns 'Waste no space   '

select trim (leading '.' from '  Waste no space   ') from rdb$database
		-- returns '  Waste no space   '

select trim (trailing '!' from 'Help!!!!') from rdb$database
		-- returns 'Help'

select trim ('la' from 'lalala I love you Ella') from rdb$database
		-- returns ' I love you El'

select trim ('la' from 'Lalala I love you Ella') from rdb$database
		-- returns 'Lalala I love you El'

Notes:

If str is a BLOB, the result is a BLOB. Otherwise, it is a VARCHAR(n) with n the formal length of str.
The substring to be removed, if specified, may not be bigger than 32767 bytes. However, if this substring is repeated at str's head or tail, the total number of bytes removed may be far greater. (The restriction on the size of the substring will be lifted in Firebird 3.)

	Warning
	When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

UPPER()

Available in: DSQL, ESQL, PSQL

Syntax:

UPPER (str)

Table 8.47. UPPER Function Parameter

Parameter	Description
str	An expression of a string type

Result type: (VAR)CHAR or BLOB

Description: Returns the upper-case equivalent of the input string. The exact result depends on the character set. With ASCII or NONE for instance, only ASCII characters are uppercased; with OCTETS, the entire string is returned unchanged. Since Firebird 2.1 this function also fully supports text BLOBs of any length and character set.

Examples:

select upper(_iso8859_1 'Débâcle')
		from rdb$database
		-- returns 'DÉBÂCLE' (before Firebird 2.0: 'DéBâCLE')

select upper(_iso8859_1 'Débâcle' collate fr_fr)
		from rdb$database
		-- returns 'DEBACLE', following French uppercasing rules

Date and Time Functions

Table of Contents

DATEADD()
DATEDIFF()
EXTRACT()

DATEADD()

Available in: DSQL, PSQL

Changed in: 2.5

Syntax:

DATEADD (<args>)

		<args>      ::=  <amount> <unit> TO <datetime>
		             | <unit>, <amount>, <datetime>

		<amount>    ::=  an integer expression (negative to subtract)
		<unit>      ::=  YEAR | MONTH | WEEK | DAY
		               | HOUR | MINUTE | SECOND | MILLISECOND
		<datetime>  ::=  a DATE, TIME or TIMESTAMP expression

Table 8.48. DATEADD Function Parameters

Parameter	Description
amount	An integer expression of the SMALLINT, INTEGER or BIGINT type. A negative value is subtracted
unit	Date/time unit
datetime	An expression of the DATE, TIME or TIMESTAMP type

Result type: DATE, TIME or TIMESTAMP

Description: Adds the specified number of years, months, weeks, days, hours, minutes, seconds or milliseconds to a date/time value. (The WEEK unit is new in 2.5.)

The result type is determined by the third argument.
With TIMESTAMP and DATE arguments, all units can be used. (Prior to Firebird 2.5, units smaller than DAY were disallowed for DATEs.)
With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Examples:

  dateadd (28 day to current_date)
		dateadd (-6 hour to current_time)
		dateadd (month, 9, DateOfConception)
		dateadd (-38 week to DateOfBirth)
		dateadd (minute, 90, time 'now')
		dateadd (? year to date '11-Sep-1973')

DATEDIFF()

Available in: DSQL, PSQL

Changed in: 2.5

Syntax:

DATEDIFF (<args>)

		<args>     ::=  <unit> FROM <moment1> TO <moment2>
		            | <unit>, <moment1>, <moment2>

		<unit>     ::=  YEAR | MONTH | WEEK | DAY
		              | HOUR | MINUTE | SECOND | MILLISECOND
		<momentN>  ::=  a DATE, TIME or TIMESTAMP expression

Table 8.49. DATEDIFF Function Parameters

Parameter	Description
unit	Date/time unit
moment1	An expression of the DATE, TIME or TIMESTAMP type
moment2	An expression of the DATE, TIME or TIMESTAMP type

Result type: BIGINT

Description: Returns the number of years, months, weeks, days, hours, minutes, seconds or milliseconds elapsed between two date/time values. (The WEEK unit is new in 2.5.)

DATE and TIMESTAMP arguments can be combined. No other mixes are allowed.
With TIMESTAMP and DATE arguments, all units can be used. (Prior to Firebird 2.5, units smaller than DAY were disallowed for DATEs.)
With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Computation:

DATEDIFF doesn't look at any smaller units than the one specified in the first argument. As a result,
- “datediff (year, date '1-Jan-2009', date '31-Dec-2009')” returns 0, but
- “datediff (year, date '31-Dec-2009', date '1-Jan-2010')” returns 1
It does, however, look at all the bigger units. So:
- “datediff (day, date '26-Jun-1908', date '11-Sep-1973')” returns 23818
A negative result value indicates that moment2 lies before moment1.

Examples:

  datediff (hour from current_timestamp to timestamp '12-Jun-2059 06:00')
		datediff (minute from time '0:00' to current_time)
		datediff (month, current_date, date '1-1-1900')
		datediff (day from current_date to cast(? as date))

EXTRACT()

Available in: DSQL, ESQL, PSQL

Syntax:

EXTRACT (<part> FROM <datetime>)

		<part>      ::=  YEAR | MONTH | WEEK
		               | DAY | WEEKDAY | YEARDAY
		               | HOUR | MINUTE | SECOND | MILLISECOND
		<datetime>  ::=  a DATE, TIME or TIMESTAMP expression

Table 8.50. EXTRACT Function Parameters

Parameter	Description
part	Date/time unit
datetime	An expression of the DATE, TIME or TIMESTAMP type

Result type: SMALLINT or NUMERIC

Description: Extracts and returns an element from a DATE, TIME or TIMESTAMP expression. This function was already added in InterBase 6, but not documented in the Language Reference at the time.

Returned Data Types and Ranges

The returned data types and possible ranges are shown in the table below. If you try to extract a part that isn't present in the date/time argument (e.g. SECOND from a DATE or YEAR from a TIME), an error occurs.

Table 8.51. Types and ranges of EXTRACT results

Part	Type	Range	Comment
YEAR	SMALLINT	1—9999
MONTH	SMALLINT	1—12
WEEK	SMALLINT	1—53
DAY	SMALLINT	1—31
WEEKDAY	SMALLINT	0—6	0 = Sunday
YEARDAY	SMALLINT	0—365	0 = January 1
HOUR	SMALLINT	0—23
MINUTE	SMALLINT	0—59
SECOND	NUMERIC(9,4)	0.0000—59.9999	includes millisecond as fraction
MILLISECOND	NUMERIC(9,1)	0.0—999.9	broken in 2.1, 2.1.1

MILLISECOND

Description: Firebird 2.1 and up support extraction of the millisecond from a TIME or TIMESTAMP. The datatype returned is NUMERIC(9,1).

	Note
	If you extract the millisecond from CURRENT_TIME, be aware that this variable defaults to seconds precision, so the result will always be 0. Extract from CURRENT_TIME(3) or CURRENT_TIMESTAMP to get milliseconds precision.

WEEK

Description: Firebird 2.1 and up support extraction of the ISO-8601 week number from a DATE or TIMESTAMP. ISO-8601 weeks start on a Monday and always have the full seven days. Week 1 is the first week that has a majority (at least 4) of its days in the new year. The first 1—3 days of the year may belong to the last week (52 or 53) of the previous year. Likewise, a year's final 1—3 days may belong to week 1 of the following year.

Caution

Be careful when combining WEEK and YEAR results. For instance, 30 December 2008 lies in week 1 of 2009, so “extract (week from date '30 Dec 2008')” returns 1. However, extracting YEAR always gives the calendar year, which is 2008. In this case, WEEK and YEAR are at odds with each other. The same happens when the first days of January belong to the last week of the previous year.

Please also notice that WEEKDAY is not ISO-8601 compliant: it returns 0 for Sunday, whereas ISO-8601 specifies 7.

Type Casting Functions

Table of Contents

CAST()

CAST()

Available in: DSQL, ESQL, PSQL

Changed in: 2.5

Syntax:

CAST (expression AS <target_type>)

		<target_type>  ::=  sql_datatype
		                | [TYPE OF] domain
		                | TYPE OF COLUMN relname.colname

Table 8.52. CAST Function Parameters

Parameter	Description
value	SQL expression
datatype	SQL data type
domain
colname	Table or view column name
precision	Precision. From 1 to 18
scale	Scale. From 0 to 18—it must be less than or equal to precision
size	The maximum size of a string in characters
charset	Character set
subtype_num	BLOB subtype number
subtype_name	BLOB subtype mnemonic name
seglen	Segment size—it cannot be greater than 65,535

Result type: User-chosen.

Description: CAST converts an expression to the desired datatype or domain. If the conversion is not possible, an error is raised.

“Shorthand” Syntax

Alternative syntax, supported only when casting a string literal to a DATE, TIME or TIMESTAMP:

datatype 'date/timestring'

This syntax was already available in InterBase, but was never properly documented.

	Note
	The short syntax is evaluated immediately at parse time, causing the value to stay the same until the statement is unprepared. For datetime literals like `'12-Oct-2012'` this makes no difference. For the pseudo-variables 'NOW', 'YESTERDAY', 'TODAY' and 'TOMORROW', this may not be what you want. If you need the value to be evaluated at every call, use the full CAST() syntax.

Examples:

A full-syntax cast:
select cast ('12' || '-June-' || '1959' as date) from rdb$database
A shorthand string-to-date cast:
update People set AgeCat = 'Old'
		where BirthDate < date '1-Jan-1943'
Notice that you can drop even the shorthand cast from the example above, as the engine will understand from the context (comparison to a DATE field) how to interpret the string:
update People set AgeCat = 'Old'
		where BirthDate < '1-Jan-1943'
But this is not always possible. The cast below cannot be dropped, otherwise the engine would find itself with an integer to be subtracted from a string:
select date 'today' - 7 from rdb$database

The following table shows the type conversions possible with CAST.

Table 8.53. Possible Type-castings with CAST

From

Numeric types

[VAR]CHAR

BLOB

[VAR]CHAR

BLOB

[VAR]CHAR

BLOB

Numeric types

DATE

TIME

TIMESTAMP

DATE

TIME

[VAR]CHAR

BLOB

TIMESTAMP

[VAR]CHAR

BLOB

DATE

TIME

Keep in mind that sometimes information is lost, for instance when you cast a TIMESTAMP to a DATE. Also, the fact that types are CAST-compatible is in itself no guarantee that a conversion will succeed. “CAST(123456789 as SMALLINT)” will definitely result in an error, as will “CAST('Judgement Day' as DATE)”.

Casting input fields: Since Firebird 2.0, you can cast statement parameters to a datatype:

cast (? as integer)

This gives you control over the type of input field set up by the engine. Please notice that with statement parameters, you always need a full-syntax cast—shorthand casts are not supported.

Casting to a domain or its type: Firebird 2.1 and above support casting to a domain or its base type. When casting to a domain, any constraints (NOT NULL and/or CHECK) declared for the domain must be satisfied or the cast will fail. Please be aware that a CHECK passes if it evaluates to TRUE or NULL! So, given the following statements:

create domain quint as int check (value >= 5000)
		select cast (2000 as quint) from rdb$database     -- (1)
		select cast (8000 as quint) from rdb$database     -- (2)
		select cast (null as quint) from rdb$database     -- (3)

only cast number (1) will result in an error.

When the TYPE OF modifier is used, the expression is cast to the base type of the domain, ignoring any constraints. With domain quint defined as above, the following two casts are equivalent and will both succeed:

select cast (2000 as type of quint) from rdb$database
		select cast (2000 as int) from rdb$database

If TYPE OF is used with a (VAR)CHAR type, its character set and collation are retained:

create domain iso20 varchar(20) character set iso8859_1;
		create domain dunl20 varchar(20) character set iso8859_1 collate du_nl;
		create table zinnen (zin varchar(20));
		commit;
		insert into zinnen values ('Deze');
		insert into zinnen values ('Die');
		insert into zinnen values ('die');
		insert into zinnen values ('deze');

		select cast(zin as type of iso20) from zinnen order by 1;
		-- returns Deze -> Die -> deze -> die

		select cast(zin as type of dunl20) from zinnen order by 1;
		-- returns deze -> Deze -> die -> Die

	Warning
	If a domain's definition is changed, existing CASTs to that domain or its type may become invalid. If these CASTs occur in PSQL modules, their invalidation may be detected. See the note The RDB$VALID_BLR field, in Appendix A.

Casting to a column's type: In Firebird 2.5 and above, it is possible to cast expressions to the type of an existing table or view column. Only the type itself is used; in the case of string types, this includes the character set but not the collation. Constraints and default values of the source column are not applied.

create table ttt (
		s varchar(40) character set utf8 collate unicode_ci_ai
		);
		commit;

		select cast ('Jag har många vänner' as type of column ttt.s) from rdb$database;

Warnings

For text types, character set and collation are preserved by the cast—just as when casting to a domain. However, due to a bug, the collation is not always taken into consideration when comparisons (e.g. equality tests) are made. In cases where the collation is of importance, test your code thoroughly before deploying! This bug is fixed for Firebird 3.
If a column's definition is altered, existing CASTs to that column's type may become invalid. If these CASTs occur in PSQL modules, their invalidation may be detected. See the note The RDB$VALID_BLR field, in Appendix A.

Casting BLOBs: Successful casting to and from BLOBs is possible since Firebird 2.1.

Functions for Bitwise Operations

Table of Contents

BIN_AND()
BIN_NOT()
BIN_OR()
BIN_SHL()
BIN_SHR()
BIN_XOR()

BIN_AND()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

BIN_AND (number, number [, number ...])

Table 8.54. BIN_AND Function Parameters

Parameter	Description
number	Any integer number (literal, smallint/integer/bigint, numeric/decimal with scale 0)

Result type: SMALLINT, INTEGER or BIGINT

	Note
	SMALLINT result is returned only if all the arguments are explicit SMALLINTs or NUMERIC(n, 0) with n <= 4; otherwise small integers return an INTEGER result.

Description: Returns the result of the bitwise AND operation on the argument(s).

BIN_NOT()

Available in: DSQL, PSQL

Possible name conflict: NO

Syntax:

BIN_NOT (number)

Table 8.55. BIN_NOT Function Parameter

Parameter	Description
number	Any integer number (literal, smallint/integer/bigint, numeric/decimal with scale 0)

Result type: SMALLINT, INTEGER or BIGINT

	Note
	SMALLINT result is returned only if all the arguments are explicit SMALLINTs or NUMERIC(n, 0) with n <= 4; otherwise small integers return an INTEGER result.

Description: Returns the result of the bitwise NOT operation on the argument, i.e., ones complement.

See also: BIN_OR, BIN_XOR and others in this set.

BIN_OR()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

BIN_OR (number, number [, number ...])

Table 8.56. BIN_OR Function Parameters

Parameter	Description
number	Any integer number (literal, smallint/integer/bigint, numeric/decimal with scale 0)

Result type: SMALLINT, INTEGER or BIGINT

	Note
	SMALLINT result is returned only if all the arguments are explicit SMALLINTs or NUMERIC(n, 0) with n <= 4; otherwise small integers return an INTEGER result.

Description: Returns the result of the bitwise OR operation on the argument(s).

BIN_SHL()

Available in: DSQL, PSQL

Syntax:

BIN_SHL (number, shift)

Table 8.57. BIN_SHL Function Parameters

Parameter	Description
number	A number of an integer type
shift	The number of bits the number value is shifted by

Result type: BIGINT

Description: Returns the first argument bitwise left-shifted by the second argument, i.e. a << b or a·2^b.

BIN_SHR()

Available in: DSQL, PSQL

Syntax:

BIN_SHR (number, shift)

Table 8.58. BIN_SHR Function Parameters

Parameter	Description
number	A number of an integer type
shift	The number of bits the number value is shifted by

Description: Returns the first argument bitwise right-shifted by the second argument, i.e. a >> b or a/2^b.

The operation performed is an arithmetic right shift (SAR), meaning that the sign of the first operand is always preserved.

Result type: BIGINT

BIN_XOR()

Available in: DSQL, PSQL

Possible name conflict: YES—>Read details

Syntax:

BIN_XOR (number, number [, number ...])

Table 8.59. BIN_XOR Function Parameters

Parameter	Description
number	Any integer number (literal, smallint/integer/bigint, numeric/decimal with scale 0)

Description: Returns the result of the bitwise XOR operation on the argument(s).

Result type: SMALLINT, INTEGER or BIGINT

	Note
	SMALLINT result is returned only if all the arguments are explicit SMALLINTs or NUMERIC(n, 0) with n <= 4; otherwise small integers return an INTEGER result.

Functions for Working with UUID

Table of Contents

CHAR_TO_UUID()
GEN_UUID()
UUID_TO_CHAR()

CHAR_TO_UUID()

Available in: DSQL, PSQL

Added in: 2.5

Syntax:

CHAR_TO_UUID (ascii_uuid)

Table 8.60. CHAR_TO_UUID Function Parameter

Parameter	Description
ascii_uuid	A 36-character representation of UUID. '-' (hyphen) in positions 9, 14, 19 and 24; valid hexadecimal digits in any other positions, e.g. 'A0bF4E45-3029-2a44-D493-4998c9b439A3'

Result type: CHAR(16) CHARACTER SET OCTETS

Description: Converts a human-readable 36-char UUID string to the corresponding 16-byte UUID.

Examples:

select char_to_uuid('A0bF4E45-3029-2a44-D493-4998c9b439A3') from rdb$database
		-- returns A0BF4E4530292A44D4934998C9B439A3 (16-byte string)

		select char_to_uuid('A0bF4E45-3029-2A44-X493-4998c9b439A3') from rdb$database
		-- error: -Human readable UUID argument for CHAR_TO_UUID must
		--         have hex digit at position 20 instead of "X (ASCII 88)"

GEN_UUID()

Available in: DSQL, PSQL

Syntax:

GEN_UUID ()

Result type: CHAR(16) CHARACTER SET OCTETS

Description: Returns a universally unique ID as a 16-byte character string.

Example:

select gen_uuid() from rdb$database
		-- returns e.g. 017347BFE212B2479C00FA4323B36320 (16-byte string)

UUID_TO_CHAR()

Available in: DSQL, PSQL

Added in: 2.5

Syntax:

UUID_TO_CHAR (uuid)

		uuid  ::=  a string consisting of 16 single-byte characters

Table 8.61. UUID_TO_CHAR Function Parameters

Parameter	Description
uuid	16-byte UUID

Result type: CHAR(36)

Description: Converts a 16-byte UUID to its 36-character, human-readable ASCII representation.

Examples:

select uuid_to_char(x'876C45F4569B320DBCB4735AC3509E5F') from rdb$database
		-- returns '876C45F4-569B-320D-BCB4-735AC3509E5F'

		select uuid_to_char(gen_uuid()) from rdb$database
		-- returns e.g. '680D946B-45FF-DB4E-B103-BB5711529B86'

		select uuid_to_char('Firebird swings!') from rdb$database
		-- returns '46697265-6269-7264-2073-77696E677321'

Functions for Working with Generators (Sequences)

Table of Contents

GEN_ID()

GEN_ID()

Available in: DSQL, ESQL, PSQL

Description: Increments a generator or sequence and returns its new value. From Firebird 2.0 onward, the SQL-compliant NEXT VALUE FOR syntax is preferred, except when an increment other than 1 is needed.

Result type: BIGINT

Syntax:

GEN_ID (generator-name, <step>)

Table 8.62. GEN_ID Function Parameters

Parameter	Description
generator-name	Name of a generator (sequence) that exists. If it has been defined in double quotes with a case-sensitive identifier, it must be used in the same form unless the name is all upper-case.
step	An integer expression

Result type: BIGINT

Description: Increments a generator or sequence and returns its new value. If step equals 0, the function will leave the value of the generator unchanged and return its current value.

From Firebird 2.0 onward, the SQL-compliant NEXT VALUE FOR syntax is preferred, except when an increment other than 1 is needed.

Example:

new.rec_id = gen_id(gen_recnum, 1);

	Warning
	If the value of the step parameter is less than zero, it will decrease the value of the generator. Attention! You should be extremely cautious with such manipulations in the database, as they could compromise data integrity.

Conditional Functions

Table of Contents

COALESCE()
DECODE()
IIF()
MAXVALUE()
MINVALUE()
NULLIF()

COALESCE()

Available in: DSQL, PSQL

Syntax:

COALESCE (<exp1>, <exp2> [, <expN> ... ])

Table 8.63. COALESCE Function Parameters

Parameter	Description
exp1, exp2 … expN	A list of expressions of any compatible types

Description: The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument. If all the arguments evaluate to NULL, the result is NULL.

Result type: Depends on input.

Example: This example picks the Nickname from the Persons table. If it happens to be NULL, it goes on to FirstName. If that too is NULL, “Mr./Mrs.” is used. Finally, it adds the family name. All in all, it tries to use the available data to compose a full name that is as informal as possible. Notice that this scheme only works if absent nicknames and first names are really NULL: if one of them is an empty string instead, COALESCE will happily return that to the caller.

select
		coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
		as FullName
		from Persons

DECODE()

Available in: DSQL, PSQL

Syntax:

DECODE(testexpr,
       expr1, result1
       expr2, result2 …]
       [, defaultresult])

The equivalent CASE construct:

CASE testexpr
       WHEN expr1 THEN result1
       [WHEN expr2 THEN result2 …]
       [ELSE defaultresult]
END

Table 8.64. DECODE Function Parameters

Parameter	Description
testexpr	An expression of any compatible type that is compared to the expressions expr1, expr2 ... exprN
expr1, expr2, … exprN	Expressions of any compatible types, to which the <testexpr> expression is compared
result1, result2, … resultN	Returned values of any type
defaultresult	The expression to be returned if none of the conditions is met

Result type: Varies

Description: DECODE is a shortcut for the so-called “simple CASE” construct, in which a given expression is compared to a number of other expressions until a match is found. The result is determined by the value listed after the matching expression. If no match is found, the default result is returned, if present. Otherwise, NULL is returned.

	Caution
	Matching is done with the “`=`” operator, so if `<testexpr>` is `NULL`, it won't match any of the `<expr>`s, not even those that are `NULL`.

Example:

select name,
		   age,
		   decode( upper(sex),
		           'M', 'Male',
		           'F', 'Female',
		           'Unknown' ),
		   religion
		from people

IIF()

Available in: DSQL, PSQL

Syntax:

IIF (<condition>, ResultT, ResultF)

Table 8.65. IIF Function Parameters

Parameter	Description
condition	A true\|false expression
resultT	The value returned if the condition is true
resultF	The value returned if the condition is false

Result type: Depends on input.

Description: IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.

IIF could be likened to the ternary “? :” operator in C-like languages.

Example:

select iif( sex = 'M', 'Sir', 'Madam' ) from Customers

	Note
	IIF(`Cond`, `Result1`, `Result2`) is a shortcut for “CASE WHEN `Cond` THEN `Result1` ELSE `Result2` END”.

MAXVALUE()

Available in: DSQL, PSQL

Syntax:

MAXVALUE (expr1 [, ... ,exprN ])

Table 8.66. MAXVALUE Function Parameters

Parameter	Description
expr1 … exprN	List of expressions of compatible types

Result type: Varies according to input—result will be of the same data type as the first expression in the list (<expr1>).

Description: Returns the maximum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.

If one or more expressions resolve to NULL, MAXVALUE returns NULL. This behaviour differs from the aggregate function MAX.

Example:

SELECT MAXVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

MINVALUE()

Available in: DSQL, PSQL

Syntax:

MINVALUE (expr1 [, ... , exprN ])

Table 8.67. MINVALUE Function Parameters

Parameter	Description
expr1 … exprN	List of expressions of compatible types

Result type: Varies according to input—result will be of the same data type as the first expression in the list (<expr1>).

Description: Returns the minimum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.

If one or more expressions resolve to NULL, MINVALUE returns NULL. This behaviour differs from the aggregate function MIN.

Example:

SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

NULLIF()

Available in: DSQL, PSQL

Syntax:

NULLIF (<exp1>, <exp2>)

Table 8.68. NULLIF Function Parameters

Parameter	Description
exp1	An expression
exp2	Another expression of a data type compatible with <exp1>

Description: NULLIF returns the value of the first argument, unless it is equal to the second. In that case, NULL is returned.

Result type: Depends on input.

Example:

select avg( nullif(Weight, -1) ) from FatPeople

This will return the average weight of the persons listed in FatPeople, excluding those having a weight of -1, since AVG skips NULL data. Presumably, -1 indicates “weight unknown” in this table. A plain AVG(Weight) would include the -1 weights, thus skewing the result.