The Firebird 5.0 Language Reference | Built-In Scalar Functions

Unless explicitly mentioned otherwise in an “Available in” section, functions are available in DSQL and PSQL.Availability of built-in functions in ESQL is not tracked by this Language Reference.

Context Functions

`RDB$GET_CONTEXT()`

Retrieves the value of a context variable from a namespace

Result type

VARCHAR(255)

Syntax

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

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

Table 1. `RDB$GET_CONTEXT` Function Parameters
Parameter	Description
namespace	Namespace
varname	Variable name;case-sensitive with a maximum length of 80 characters

The namespaces

The USER_SESSION and USER_TRANSACTION namespaces are initially empty.A user can create and set variables with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT().The SYSTEM namespace is read-only.The DDL_TRIGGER namespace is only valid in DDL triggers, and is read-only.The SYSTEM and DDL_TRIGGER namespaces contain a number of predefined variables, shown below.

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 or DDL_TRIGGER namespace, an error is raised.If you request a non-existing variable in one of the user namespaces, NULL is returned.Both namespace and variable names must be given as single-quoted, case-sensitive, non-NULL strings.

The `SYSTEM` Namespace

Context variables in the SYSTEM namespace

CLIENT_ADDRESS: For TCP, this is the IP address.For XNET, the local process ID.For all other protocols this variable is NULL.
CLIENT_HOST: The wire protocol host name of remote client.Value is returned for all supported protocols.
CLIENT_PID: Process ID of remote client application.
CLIENT_PROCESS: Process name of remote client application.
CURRENT_ROLE: Same as global [fblangref50-contextvars-current-role] variable.
CURRENT_USER: Same as global [fblangref50-contextvars-current-user] variable.
DB_FILE_ID: Unique filesystem-level ID of the current database.
DB_GUID: GUID of the current database.
DB_NAME: Canonical name of current database;either the full path to the database or — if connecting via the path is disallowed — its alias.
DECFLOAT_ROUND: Rounding mode of the current connection used in operations with DECFLOAT values.See also SET DECFLOAT.
DECFLOAT_TRAPS: Exceptional conditions for the current connection in operations with DECFLOAT values that cause a trap.See also SET DECFLOAT.
EFFECTIVE_USER: Effective user at the point RDB$GET_CONTEXT is called;indicates privileges of which user is currently used to execute a function, procedure, trigger.
ENGINE_VERSION: The Firebird engine (server) version.
EXT_CONN_POOL_ACTIVE_COUNT: Count of active connections associated with the external connection pool.
EXT_CONN_POOL_IDLE_COUNT: Count of currently inactive connections available in the connection pool.
EXT_CONN_POOL_LIFETIME: External connection pool idle connection lifetime, in seconds.
EXT_CONN_POOL_SIZE: External connection pool size.
GLOBAL_CN: Most current value of global Commit Number counter.
ISOLATION_LEVEL: The isolation level of the current transaction: 'READ COMMITTED', 'SNAPSHOT' or 'CONSISTENCY'.
LOCK_TIMEOUT: Lock timeout of the current transaction.
NETWORK_PROTOCOL: The protocol used for the connection: 'TCPv4', 'TCPv6', 'XNET' or NULL.
PARALLEL_WORKERS: The maximum number of parallel workers of the connection.
READ_ONLY: Returns 'TRUE' if current transaction is read-only and 'FALSE' otherwise.
REPLICA_MODE: Replica mode of the database: 'READ-ONLY', 'READ-WRITE' and NULL.
REPLICATION_SEQUENCE: Current replication sequence (number of the latest segment written to the replication journal).
SESSION_ID: Same as global [fblangref50-contextvars-current-connection] variable.
SESSION_IDLE_TIMEOUT: Connection-level idle timeout, or 0 if no timeout was set.When 0 is reported the database ConnectionIdleTimeout from databases.conf or firebird.conf applies.
SESSION_TIMEZONE: Current session time zone.
SNAPSHOT_NUMBER: Current snapshot number for the transaction executing this statement.For SNAPSHOT and SNAPSHOT TABLE STABILITY, this number is stable for the duration of the transaction;for READ COMMITTED this number will change (increment) as concurrent transactions are committed.
STATEMENT_TIMEOUT: Connection-level statement timeout, or 0 if no timeout was set.When 0 is reported the database StatementTimeout from databases.conf or firebird.conf applies.
TRANSACTION_ID: Same as global [fblangref50-contextvars-current-transaction] variable.
WIRE_COMPRESSED: Compression status of the current connection.If the connection is compressed, returns TRUE;if it is not compressed, returns FALSE.Returns NULL if the connection is embedded.
WIRE_CRYPT_PLUGIN: If connection is encrypted - returns name of current plugin, otherwise NULL.
WIRE_ENCRYPTED: Encryption status of the current connection.If the connection is encrypted, returns TRUE;if it is not encrypted, returns FALSE.Returns NULL if the connection is embedded.

The `DDL_TRIGGER` Namespace

The DDL_TRIGGER namespace is valid only when a DDL trigger is running.Its use is also valid in stored procedures and functions when called by DDL triggers.

The DDL_TRIGGER context works like a stack.Before a DDL trigger is fired, the values relative to the executed command are pushed onto this stack.After the trigger finishes, the values are popped.So in the case of cascade DDL statements, when a user DDL command fires a DDL trigger and this trigger executes another DDL command with EXECUTE STATEMENT, the values of the DDL_TRIGGER namespace are the ones relative to the command that fired the last DDL trigger on the call stack.

Context variables in the DDL_TRIGGER namespace

EVENT_TYPE: event type (CREATE, ALTER, DROP)
OBJECT_TYPE: object type (TABLE, VIEW, etc)
DDL_EVENT: event name (<ddl event item>), where <ddl event item> is EVENT_TYPE || ' ' || OBJECT_TYPE
OBJECT_NAME: metadata object name
OLD_OBJECT_NAME: for tracking the renaming of a domain (see note)
NEW_OBJECT_NAME: for tracking the renaming of a domain (see note)
SQL_TEXT: sql statement text

Note	`ALTER DOMAIN old-name TO new-name` sets `OLD_OBJECT_NAME` and `NEW_OBJECT_NAME` in both `BEFORE` and `AFTER` triggers.For this command, `OBJECT_NAME` will have the old object name in `BEFORE` triggers, and the new object name in `AFTER` triggers.

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'))

See also

[fblangref50-scalarfuncs-set-context]

`RDB$SET_CONTEXT()`

Creates, sets or clears a variable in one of the user-writable namespaces

Result type

INTEGER

Syntax

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

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

Table 1. `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)`

The namespaces

The USER_SESSION and USER_TRANSACTION namespaces are initially empty.A user can create and set variables with RDB$SET_CONTEXT() and retrieve them with [fblangref50-scalarfuncs-get-context].The USER_SESSION context is bound to the current connection, the USER_TRANSACTION context to the current transaction.

Lifecycle

When a transaction ends, its USER_TRANSACTION context is cleared.
When a connection is closed, its USER_SESSION context is cleared.
When a connection is reset using ALTER SESSION RESET, the USER_TRANSACTION and USER_SESSION contexts are cleared.

Return values and error behaviour

The function returns 1 when the variable already existed before the call and 0 when 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.

Note

The maximum number of variables in any single context is 1000.
All USER_TRANSACTION variables 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.
ALTER SESSION RESET clears both USER_TRANSACTION and USER_SESSION contexts.

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

See also

[fblangref50-scalarfuncs-get-context]

Special Functions for `DECFLOAT`

`COMPARE_DECFLOAT()`

Compares two DECFLOAT values to be equal, different or unordered

Result type

SMALLINT

Syntax

COMPARE_DECFLOAT (decfloat1, decfloat2)

Table 1. `COMPARE_DECFLOAT` Function Parameters
Parameter	Description
decfloatn	Value or expression of type `DECFLOAT`, or cast-compatible with `DECFLOAT`

The result is a SMALLINT value, as follows:

`0`	Values are equal
`1`	First value is less than second
`2`	First value is greater than second
`3`	Values are unordered, i.e. one or both is `NaN`/`sNaN`

Unlike the comparison operators (‘<’, ‘=’, ‘>’, etc.), comparison is exact: COMPARE_DECFLOAT(2.17, 2.170) returns 2 not 0.

See also

[fblangref50-scalarfuncs-totalorder]

`NORMALIZE_DECFLOAT()`

Returns the simplest, normalized form of a DECFLOAT

Result type

DECFLOAT

Syntax

NORMALIZE_DECFLOAT (decfloat_value)

Table 1. `NORMALIZE_DECFLOAT` Function Parameters
Parameter	Description
decfloat_value	Value or expression of type `DECFLOAT`, or cast-compatible with `DECFLOAT`

For any non-zero value, trailing zeroes are removed with appropriate correction of the exponent.

Examples of `NORMALIZE_DECFLOAT`

-- will return 12
select normalize_decfloat(12.00)
from rdb$database;

-- will return 1.2E+2
select normalize_decfloat(120)
from rdb$database;

`QUANTIZE()`

Returns a value that is equal in value — except for rounding — to the first argument, but with the same exponent as the second argument

Result type

DECFLOAT

Syntax

QUANTIZE (decfloat_value, exp_value)

Table 1. `QUANTIZE` Function Parameters
Parameter	Description
decfloat_value	Value or expression to quantize;needs to be of type `DECFLOAT`, or cast-compatible with `DECFLOAT`
exp_value	Value or expression to use for its exponent;needs to be of type `DECFLOAT`, or cast-compatible with `DECFLOAT`

QUANTIZE returns a DECFLOAT value that is equal in value and sign (except for rounding) to decfloat_value, and that has an exponent equal to the exponent of exp_value.The type of the return value is DECFLOAT(16) if both arguments are DECFLOAT(16), otherwise the result type is DECFLOAT(34).

Note

The target exponent is the exponent used in the Decimal64 or Decimal128 storage format of DECFLOAT of exp_value.This is not necessarily the same as the exponent displayed in tools like isql.For example, the value 1.23E+2 is coefficient 123 and exponent 0, while 1.2 is coefficient 12 and exponent -1.

If the exponent of decfloat_value is greater than the one of exp_value, the coefficient of decfloat_value is multiplied by a power of ten, and its exponent decreased.If the exponent is smaller, then its coefficient is rounded using the current decfloat rounding mode, and its exponent is increased.

When it is not possible to achieve the target exponent because the coefficient would exceed the target precision (16 or 34 decimal digits), either a “Decfloat float invalid operation” error is raised or NaN is returned (depending on the current decfloat traps configuration).

There are almost no restrictions on the exp_value.However, in almost all usages, NaN/sNaN/Infinity will produce an exception (unless allowed by the current decfloat traps configuration), NULL will make the function return NULL, and so on.

Examples of `QUANTIZE`

select v, pic, quantize(v, pic) from examples;

     V    PIC QUANTIZE
====== ====== ========
  3.16  0.001    3.160
  3.16   0.01     3.16
  3.16    0.1      3.2
  3.16      1        3
  3.16   1E+1     0E+1
  -0.1      1       -0
     0   1E+5     0E+5
   316    0.1    316.0
   316      1      316
   316   1E+1   3.2E+2
   316   1E+2     3E+2

`TOTALORDER()`

Determines the total or linear order of its arguments

Result type

SMALLINT

Syntax

TOTALORDER (decfloat1, decfloat2)

Table 1. `TOTALORDER` Function Parameters
Parameter	Description
decfloatn	Value or expression of type `DECFLOAT`, or cast-compatible with `DECFLOAT`

TOTALORDER compares two DECFLOAT values including any special values.The comparison is exact, and returns a SMALLINT, one of:

`-1`	First value is less than second
`0`	Values are equal
`1`	First value is greater than second.

For TOTALORDER comparisons, DECFLOAT values are ordered as follows:

-NaN < -sNaN < -INF < -0.1 < -0.10 < -0 < 0 < 0.10 < 0.1 < INF < sNaN < NaN

Cryptographic Functions

`DECRYPT()`

Decrypts data using a symmetric cipher

Result type

VARBINARY or BLOB

Syntax

DECRYPT ( encrypted_input
  USING <algorithm> [MODE <mode>]
  KEY key
  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
  [COUNTER initial_counter] )

!! See syntax of <<fblangref50-scalarfuncs-encrypt,ENCRYPT>> for further rules !!

Table 1. `DECRYPT` Function Parameters
Parameter	Description
encrypted_input	Encrypted input as a blob or (binary) string
See [fblangref50-scalarfuncs-tbl-encrypt] for other parameters

Note

Sizes of data strings (like encrypted_input, key and iv) must meet the requirements of the selected algorithm and mode.
This function returns BLOB SUB_TYPE BINARY when the first argument is a BLOB, and VARBINARY for all other text and binary types.
When the encrypted data was text, it must be explicitly cast to a string type of appropriate character set.
The ins and outs of the various algorithms are considered beyond the scope of this language reference.We recommend searching the internet for further details on the algorithms.

`DECRYPT` Examples

select decrypt(x'0154090759DF' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
  from rdb$database;
select decrypt(secret_field using aes mode ofb key '0123456701234567' iv init_vector)
  from secure_table;

`ENCRYPT()`

Encrypts data using a symmetric cipher

Result type

VARBINARY or BLOB

Syntax

ENCRYPT ( input
  USING <algorithm> [MODE <mode>]
  KEY key
  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
  [COUNTER initial_counter] )

<algorithm> ::= <block_cipher> | <stream_cipher>

<block_cipher> ::=
    AES | ANUBIS | BLOWFISH | KHAZAD | RC5
  | RC6 | SAFER+ | TWOFISH | XTEA

<stream_cipher> ::= CHACHA20 | RC4 | SOBER128

<mode> ::= CBC | CFB | CTR | ECB | OFB

<ctr_type> ::= CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN

Table 1. `ENCRYPT` Function Parameters
Parameter	Description
input	Input to encrypt as a blob or (binary) string
algorithm	The algorithm to use for decryption
mode	The algorithm mode;only for block ciphers
key	The encryption/decryption key
iv	Initialization vector or nonce;should be specified for block ciphers in all modes except `ECB`, and all stream ciphers except `RC4`
ctr_type	Endianness of the counter;only for `CTR` mode.Default is `CTR_LITTLE_ENDIAN`.
ctr_length	Counter length;only for `CTR` mode.Default is size of iv.
initial_counter	Initial counter value;only for `CHACHA20`.Default is `0`.

Note

This function returns BLOB SUB_TYPE BINARY when the first argument is a BLOB, and VARBINARY for all other text and binary types.
Sizes of data strings (like key and iv) must meet the requirements of the selected algorithm and mode, see table [fblangref50-scalarfuncs-tbl-encrypt-req].
- In general, the size of iv must match the block size of the algorithm
- For ECB and CBC mode, input must be multiples of the block size, you will need to manually pad with zeroes or spaces as appropriate.
The ins and outs of the various algorithms and modes are considered beyond the scope of this language reference.We recommend searching the internet for further details on the algorithms.
Although specified as separate options in this Language Reference, in the actual syntax CTR_LENGTH and COUNTER are aliases.

Table 2. Encryption Algorithm Requirements
Algorithm	Key size (bytes)	Block size (bytes)	Notes
Block Ciphers
`AES`	16, 24, 32	16	Key size determines the AES variant: 16 bytes → AES-128 24 bytes → AES-192 32 bytes → AES-256
`ANUBIS`	16 - 40, in steps of 4 (4x)	16
`BLOWFISH`	8 - 56	8
`KHAZAD`	16	8
`RC5`	8 - 128	8
`RC6`	8 - 128	16
`SAFER+`	16, 24, 32	16
`TWOFISH`	16, 24, 32	16
`XTEA`	16	8
Stream Ciphers
`CHACHA20`	16, 32	1	Nonce size (IV) is 8 or 12 bytes.For nonce size 8, initial_counter is a 64-bit integer, for size 12, 32-bit.
`RC4`	5 - 256	1
`SOBER128`	4x	1	Nonce size (IV) is 4y bytes, the length is independent of key size.

`ENCRYPT` Examples

select encrypt('897897' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
  from rdb$database;

`RSA_DECRYPT()`

Decrypts data using an RSA private key and removes OAEP or PKCS 1.5 padding

Result type

VARBINARY

Syntax

RSA_DECRYPT (encrypted_input KEY private_key
  [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])

<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 1. `RSA_DECRYPT` Function Parameters
Parameter	Description
encrypted_input	Input data to decrypt
private_key	Private key to apply, PKCS#1 format
tag_string	An additional system-specific tag to identify which system encrypted the message;default is `NULL`.If the tag does not match what was used during encryption, `RSA_DECRYPT` will not decrypt the data.
hash	The hash used for OAEP padding;default is `SHA256`.

RSA_DECRYPT decrypts encrypted_input using the RSA private key and then removes padding from the resulting data.

By default, OAEP padding is used.The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding.For security reasons, it should not be used in new projects.

Note	This function returns `VARBINARY`. When the encrypted data was text, it must be explicitly cast to a string type of appropriate character set.

`RSA_DECRYPT` Examples

Tip	Run the examples of the `RSA_PRIVATE` and `RSA_PUBLIC`, `RSA_ENCRYPT` functions first.

select cast(rsa_decrypt(rdb$get_context('USER_SESSION', 'msg')
  key rdb$get_context('USER_SESSION', 'private_key')) as varchar(128))
from rdb$database;

`RSA_ENCRYPT()`

Pads data using OAEP or PKCS 1.5 and then encrypts it with an RSA public key

Result type

VARBINARY

Syntax

RSA_ENCRYPT (input KEY public_key
  [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])

<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 1. `RSA_ENCRYPT` Function Parameters
Parameter	Description
input	Input data to encrypt
public_key	Public key to apply, PKCS#1 format
tag_string	An additional system-specific tag to identify which system encrypted the message;default is `NULL`.
hash	The hash used for OAEP padding;default is `SHA256`.

RSA_ENCRYPT pads input using the OAEP or PKCS 1.5 padding scheme and then encrypts it using the specified RSA public key.This function is normally used to encrypt short symmetric keys which are then used in block ciphers to encrypt a message.

By default, OAEP padding is used.The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding.For security reasons, it should not be used in new projects.

`RSA_ENCRYPT` Examples

Tip	Run the examples of the `RSA_PRIVATE` and `RSA_PUBLIC` functions first.

select rdb$set_context('USER_SESSION', 'msg', rsa_encrypt('Some message'
  key rdb$get_context('USER_SESSION', 'public_key'))) from rdb$database;

`RSA_PRIVATE()`

Generates an RSA private key

Result type

VARBINARY

Syntax

RSA_PRIVATE (key_length)

Table 1. `RSA_PRIVATE` Function Parameters
Parameter	Description
key_length	Key length in bytes;minimum 4, maximum 1024.A size of 256 bytes (2048 bits) or larger is recommended.

RSA_PRIVATE generates an RSA private key of the specified length (in bytes) in PKCS#1 format.

Note	The larger the length specified, the longer it takes for the function to generate a private key.

`RSA_PRIVATE` Examples

select rdb$set_context('USER_SESSION', 'private_key', rsa_private(256))
  from rdb$database;

Warning

Putting private keys in the context variables is not secure;we’re doing it here for demonstration purposes.SYSDBA and users with the role RDB$ADMIN or the system privilege MONITOR_ANY_ATTACHMENT can see all context variables from all attachments.

`RSA_PUBLIC()`

Generates an RSA public key

Result type

VARBINARY

Syntax

RSA_PUBLIC (private_key)

Table 1. `RSA_PUBLIC` Function Parameters
Parameter	Description
private_key	RSA private key in PKCS#1 format

RSA_PUBLIC returns the RSA public key in PKCS#1 format for the provided RSA private key (also PKCS#1 format).

`RSA_PUBLIC` Examples

Tip	Run the example of the `RSA_PRIVATE` function first.

select rdb$set_context('USER_SESSION', 'public_key',
  rsa_public(rdb$get_context('USER_SESSION', 'private_key'))) from rdb$database;

`RSA_SIGN_HASH()`

PSS encodes a message hash and signs it with an RSA private key

Result type

VARBINARY

Syntax

RSA_SIGN_HASH (message_digest
  KEY private_key
  [HASH <hash>] [SALT_LENGTH salt_length]
  [PKCS_1_5])

<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 1. `RSA_SIGN_HASH` Function Parameters
Parameter	Description
message_digest	Hash of message to sign.The hash algorithm used should match hash
private_key	RSA private key in PKCS#1 format
hash	Hash to generate PSS encoding;default is `SHA256`.This should be the same hash as used to generate message_digest.
salt_length	Length of the desired salt in bytes;default is 8;minimum 1, maximum 32.The recommended value is between 8 and 16.

RSA_SIGN_HASH performs PSS encoding of the message_digest to be signed, and signs using the RSA private key.

By default, OAEP padding is used.The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding.For security reasons, it should not be used in new projects.

Caution

This function expects the hash of a message (or message digest), not the actual message.The hash argument should specify the algorithm that was used to generate that hash.

A function that accepts the actual message to hash might be introduced in a future version of Firebird.

PSS encoding

Probabilistic Signature Scheme (PSS) is a cryptographic signature scheme specifically developed to allow modern methods of security analysis to prove that its security directly relates to that of the RSA problem.There is no such proof for the traditional PKCS#1 v1.5 scheme.

`RSA_SIGN_HASH` Examples

Tip	Run the example of the `RSA_PRIVATE` function first.

select rdb$set_context('USER_SESSION', 'msg',
  rsa_sign_hash(crypt_hash('Test message' using sha256)
    key rdb$get_context('USER_SESSION', 'private_key'))) from rdb$database;

`RSA_VERIFY_HASH()`

Verifies a message hash against a signature using an RSA public key

Result type

BOOLEAN

Syntax

RSA_VERIFY_HASH (message_digest
  SIGNATURE signature KEY public_key
  [HASH <hash>] [SALT_LENGTH salt_length]
  [PKCS_1_5])

<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 1. `RSA_VERIFY` Function Parameters
Parameter	Description
message_digest	Hash of message to verify.The hash algorithm used should match hash
signature	Expected signature of input generated by `RSA_SIGN_HASH`
public_key	RSA public key in PKCS#1 format matching the private key used to sign
hash	Hash to use for the message digest;default is `SHA256`.This should be the same hash as used to generate message_digest, and as used in `RSA_SIGN_HASH`
salt_length	Length of the salt in bytes;default is 8;minimum 1, maximum 32.Value must match the length used in `RSA_SIGN_HASH`.

RSA_VERIFY_HASH performs PSS encoding of the message_digest to be verified, and verifies the digital signature using the provided RSA public key.

By default, OAEP padding is used.The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding.For security reasons, it should not be used in new projects.

Caution

This function expects the hash of a message (or message digest), not the actual message.The hash argument should specify the algorithm that was used to generate that hash.

A function that accepts the actual message to hash might be introduced in a future version of Firebird.

`RSA_VERIFY_HASH` Examples

Tip	Run the examples of the `RSA_PRIVATE`, `RSA_PUBLIC` and `RSA_SIGN_HASH` functions first.

select rsa_verify_hash(
  crypt_hash('Test message' using sha256)
  signature rdb$get_context('USER_SESSION', 'msg')
  key rdb$get_context('USER_SESSION', 'public_key'))
from rdb$database

Other Functions

Functions that don’t fit in any other category.

`MAKE_DBKEY()`

Creates a DBKEY value

Result type

BINARY(8)

Syntax

MAKE_DBKEY (relation, recnum [, dpnum [, ppnum]])

Table 1. `RDB$GET_TRANSACTION_CN` Function Parameters
Parameter	Description
relation	Relation name or relation id
recnum	Record number.Either absolute (if dpnum and ppnum are absent), or relative (if dpnum present)
dpnum	Data page number.Either absolute (if ppnum is absent) or relative (if ppnum present)
ppnum	Pointer page number.

MAKE_DBKEY creates a DBKEY value using a relation name or ID, record number, and (optionally) logical numbers of data page and pointer page.

Note

If relation is a string expression or literal, then it is treated as a relation name, and the engine searches for the corresponding relation ID.The search is case-sensitive.In the case of string literal, relation ID is evaluated at query preparation time.In the case of expression, relation ID is evaluated at execution time.If the relation cannot be found, then error isc_relnotdef is raised.
If relation is a numeric expression or literal, then it is treated as a relation ID and used “as is”, without verification against existing relations.If the argument value is negative or greater than the maximum allowed relation ID (65535 currently), then NULL is returned.
Argument recnum represents an absolute record number in the relation (if the next arguments dpnum and ppnum are missing), or a record number relative to the first record, specified by the next arguments.
Argument dpnum is a logical number of data page in the relation (if the next argument ppnum is missing), or number of data pages relative to the first data page addressed by the given ppnum.
Argument ppnum is a logical number of pointer page in the relation.
All numbers are zero-based.Maximum allowed value for dpnum and ppnum is 2³² (4294967296).If dpnum is specified, then recnum can be negative.If dpnum is missing and recnum is negative, then NULL is returned.If ppnum is specified, then dpnum can be negative.If ppnum is missing and dpnum is negative, then NULL is returned.
If any of specified arguments is NULL, the result is also NULL.
Argument relation is described as INTEGER during query preparation, but it can be overridden by a client application as VARCHAR or CHAR.Arguments recnum, dpnum and ppnum are described as BIGINT.

Examples of `MAKE_DBKEY`

Select record using relation name (note that relation name is uppercase)

select *
from rdb$relations
where rdb$db_key = make_dbkey('RDB$RELATIONS', 0)

Select record using relation ID

select *
from rdb$relations
where rdb$db_key = make_dbkey(6, 0)

Select all records physically residing on the first data page

select *
from rdb$relations
where rdb$db_key >= make_dbkey(6, 0, 0)
and rdb$db_key < make_dbkey(6, 0, 1)

Select all records physically residing on the first data page of 6th pointer page

select *
from SOMETABLE
where rdb$db_key >= make_dbkey('SOMETABLE', 0, 0, 5)
and rdb$db_key < make_dbkey('SOMETABLE', 0, 1, 5)

`RDB$ERROR()`

Returns PSQL error information inside a WHEN … DO block

Available in

PSQL

Result type

Varies (see table below)

Syntax

RDB$ERROR (<context>)

<context> ::=
  GDSCODE | SQLCODE | SQLSTATE | EXCEPTION | MESSAGE

Table 1. Contexts
Context	Result type	Description
`GDSCODE`	`INTEGER`	Firebird error code, see also `GDSCODE`
`SQLCODE`	`INTEGER`	(deprecated) SQL code, see also `SQLCODE`
`SQLSTATE`	`CHAR(5) CHARACTER SET ASCII`	SQLstate, see also `SQLSTATE`
`EXCEPTION`	`VARCHAR(63) CHARACTER SET UTF8`	Name of the active user-defined exception or `NULL` if the active exception is a system exception
`MESSAGE`	`VARCHAR(1024) CHARACTER SET UTF8`	Message text of the active exception

RDB$ERROR returns data of the specified context about the active PSQL exception.Its scope is confined to exception-handling blocks in PSQL (WHEN … DO).Outside the exception handling blocks, RDB$ERROR always returns NULL.This function cannot be called from DSQL.

Example of RDB$ERROR

BEGIN
  ...
WHEN ANY DO
  EXECUTE PROCEDURE P_LOG_EXCEPTION(RDB$ERROR(MESSAGE));
END

`RDB$GET_TRANSACTION_CN()`

Returns the commit number (“CN”) of a transaction

Result type

BIGINT

Syntax

RDB$GET_TRANSACTION_CN (transaction_id)

Table 1. `RDB$GET_TRANSACTION_CN` Function Parameters
Parameter	Description
transaction_id	Transaction id

If the return value is greater than 1, it is the actual CN of the transaction if it was committed after the database was started.

The function can also return one of the following results, indicating the commit status of the transaction:

`-2`	Transaction is dead (rolled back)
`-1`	Transaction is in limbo
`0`	Transaction is still active
`1`	Transaction committed before the database started or less than the Oldest Interesting Transaction for the database
`NULL`	Transaction number supplied is NULL or greater than Next Transaction for the database

Note	For more information about CN, consult theFirebird 4.0 Release Notes.

`RDB$GET_TRANSACTION_CN` Examples

select rdb$get_transaction_cn(current_transaction) from rdb$database;
select rdb$get_transaction_cn(123) from rdb$database;

`RDB$ROLE_IN_USE()`

Checks if a role is active for the current connection

Result type

BOOLEAN

Syntax

RDB$ROLE_IN_USE (role_name)

Table 1. `RDB$ROLE_IN_USE` Function Parameters
Parameter	Description
role_name	String expression for the role to check.Case-sensitive, must match the role name as stored in `RDB$ROLES`

RDB$ROLE_IN_USE returns TRUE if the specified role is active for the current connection, and FALSE otherwise.Contrary to CURRENT_ROLE — which only returns the explicitly specified role — this function can be used to check for roles that are active by default, or cumulative roles activated by an explicitly specified role.

`RDB$ROLE_IN_USE` Examples

List currently active roles

select rdb$role_name
from rdb$roles
where rdb$role_in_use(rdb$role_name);

See also

CURRENT_ROLE

`RDB$SYSTEM_PRIVILEGE()`

Checks if the authorization of the current connection has a system privilege

Result type

BOOLEAN

Syntax

RDB$SYSTEM_PRIVILEGE (<sys_privilege>)

<sys_privilege> ::=
  !! See CREATE ROLE !!

Table 1. `RDB$SYSTEM_PRIVILEGE` Function Parameters
Parameter	Description
sys_privilege	System privilege

RDB$SYSTEM_PRIVILEGE accepts a system privilege name and returns TRUE if the current connection has the given system privilege, and FALSE otherwise.

The authorization of the current connection is determined by privileges of the current user, the user PUBLIC, and the currently active roles (explicitly set or activated by default).

`RDB$SYSTEM_PRIVILEGE` Examples

select rdb$system_privilege(user_management) from rdb$database;

See also

Fine-grained System Privileges

Mathematical Functions

`ABS()`

Absolute value

Result type

Numerical, matching input type

Syntax

ABS (number)

Table 1. `ABS` Function Parameter
Parameter	Description
number	An expression of a numeric type

`COS()`

Cosine

Result type

DOUBLE PRECISION

Syntax

COS (angle)

Table 1. `COS` Function Parameter
Parameter	Description
angle	An angle in radians

The result is in the range [-1, 1].

`COSH()`

Hyperbolic cosine

Result type

DOUBLE PRECISION

Syntax

COSH (number)

Table 1. `COSH` Function Parameter
Parameter	Description
number	A number of a numeric type

The result is in the range [1, INF].

`COT()`

Cotangent

Result type

DOUBLE PRECISION

Syntax

COT (angle)

Table 1. `COT` Function Parameter
Parameter	Description
angle	An angle in radians

`EXP()`

Natural exponent

Result type

DOUBLE PRECISION

Syntax

EXP (number)

Table 1. `EXP` Function Parameter
Parameter	Description
number	A number of a numeric type

Returns the natural exponential, e^number

See also

[fblangref50-scalarfuncs-ln]

`FLOOR()`

Floor of a number

Result type

BIGINT or INT128 for exact numeric number, or DOUBLE PRECISION or DECFLOAT for floating point number

Syntax

FLOOR (number)

Table 1. `FLOOR` Function Parameter
Parameter	Description
number	An expression of a numeric type

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

`LN()`

Natural logarithm

Result type

DOUBLE PRECISION

Syntax

LN (number)

Table 1. `LN` Function Parameter
Parameter	Description
number	An expression of a numeric type

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

`LOG()`

Logarithm with variable base

Result type

DOUBLE PRECISION

Syntax

LOG (x, y)

Table 1. `LOG` Function Parameters
Parameter	Description
x	Base.An expression of a numeric type
y	An expression of a numeric type

Returns the x-based logarithm of y.

If either argument is 0 or below, an error is raised.
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()`

Decimal (base-10) logarithm

Result type

DOUBLE PRECISION

Syntax

LOG10 (number)

Table 1. `LOG10` Function Parameter
Parameter	Description
number	An expression of a numeric type

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

`MOD()`

Remainder

Result type

SMALLINT, INTEGER or BIGINT depending on the type of a.If a is a floating-point type, the result is a BIGINT.

Syntax

MOD (a, b)

Table 1. `MOD` Function Parameters
Parameter	Description
a	An expression of a numeric type
b	An expression of a numeric type

Returns the remainder of an integer division.

Non-integer arguments are rounded before the division takes place.So, “mod(7.5, 2.5)” gives 2 (“mod(8, 3)”), not 0.
Do not confuse MOD() with the mathematical modulus operator;e.g. mathematically, -21 mod 4 is 3, while Firebird’s MOD(-21, 4) is -1.In other words, MOD() behaves as % in languages like C and Java.

`PI()`

Approximation of pi.

Result type

DOUBLE PRECISION

Syntax

PI ()

`ACOS()`

Arc cosine

Result type

DOUBLE PRECISION

Syntax

ACOS (number)

Table 1. `ACOS` Function Parameter
Parameter	Description
number	An expression of a numeric type within the range [-1, 1]

The result is an angle in the range [0, pi].

`POWER()`

Power

Result type

DOUBLE PRECISION

Syntax

POWER (x, y)

Table 1. `POWER` Function Parameters
Parameter	Description
x	An expression of a numeric type
y	An expression of a numeric type

Returns x to the power of y (x^y).

`RAND()`

Generates a random number

Result type

DOUBLE PRECISION

Syntax

RAND ()

Returns a random number between 0 and 1.

`ROUND()`

Result type

single argument: integer type, DOUBLE PRECISION or DECFLOAT;
two arguments: numerical, matching first argument

Syntax

ROUND (number [, scale])

Table 1. `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

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.).

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.

`ROUND` 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()`

Sign or signum

Result type

SMALLINT

Syntax

SIGN (number)

Table 1. `SIGN` Function Parameter
Parameter	Description
number	An expression of a numeric type

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

number < 0 → -1
number = 0 → 0
number > 0 → 1

`SIN()`

Sine

Result type

DOUBLE PRECISION

Syntax

SIN (angle)

Table 1. `SIN` Function Parameter
Parameter	Description
angle	An angle, in radians

The result is in the range [-1, 1].

`SINH()`

Hyperbolic sine

Result type

DOUBLE PRECISION

Syntax

SINH (number)

Table 1. `SINH` Function Parameter
Parameter	Description
number	An expression of a numeric type

`SQRT()`

Square root

Result type

DOUBLE PRECISION

Syntax

SQRT (number)

Table 1. `SQRT` Function Parameter
Parameter	Description
number	An expression of a numeric type

If number is negative, an error is raised.

See also

[fblangref50-scalarfuncs-power]

`TAN()`

Tangent

Result type

DOUBLE PRECISION

Syntax

TAN (angle)

Table 1. `TAN` Function Parameter
Parameter	Description
angle	An angle, in radians

`TANH()`

Hyperbolic tangent

Result type

DOUBLE PRECISION

Syntax

TANH (number)

Table 1. `TANH` Function Parameters
Parameter	Description
number	An expression of a numeric type

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

`TRUNC()`

Truncate number

Result type

single argument: integer type, DOUBLE PRECISION or DECFLOAT;
two arguments: numerical, matching first argument

Syntax

TRUNC (number [, scale])

Table 1. `TRUNC` Function Parameters
Parameter	Description
number	An expression of a numeric type
scale	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

The single argument variant 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.).

Note

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.

`ACOSH()`

Inverse hyperbolic cosine

Result type

DOUBLE PRECISION

Syntax

ACOSH (number)

Table 1. `ACOSH` Function Parameter
Parameter	Description
number	Any non-`NULL` value in the range [1, INF].

The result is in the range [0, INF].

`ASIN()`

Arc sine

Result type

DOUBLE PRECISION

Syntax

ASIN (number)

Table 1. `ASIN` Function Parameter
Parameter	Description
number	An expression of a numeric type within the range [-1, 1]

The result is an angle in the range [-pi/2, pi/2].

`ASINH()`

Inverse hyperbolic sine

Result type

DOUBLE PRECISION

Syntax

ASINH (number)

Table 1. `ASINH` Function Parameter
Parameter	Description
number	Any non-`NULL` value in the range [-INF, INF].

The result is in the range [-INF, INF].

`ATAN()`

Arc tangent

Result type

DOUBLE PRECISION

Syntax

ATAN (number)

Table 1. `ATAN` Function Parameter
Parameter	Description
number	An expression of a numeric type

The result is an angle in the range <-pi/2, pi/2>.

`ATAN2()`

Two-argument arc tangent

Result type

DOUBLE PRECISION

Syntax

ATAN2 (y, x)

Table 1. `ATAN2` Function Parameters
Parameter	Description
y	An expression of a numeric type
x	An expression of a numeric type

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.An error will be raised if both arguments are 0.

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.

`ATANH()`

Inverse hyperbolic tangent

Result type

DOUBLE PRECISION

Syntax

ATANH (number)

Table 1. `ATANH` Function Parameter
Parameter	Description
number	Any non-`NULL` value in the range <-1, 1>.

The result is a number in the range [-INF, INF].

`CEIL()`, `CEILING()`

Ceiling of a number

Result type

BIGINT or INT128 for exact numeric number, or DOUBLE PRECISION or DECFLOAT for floating point number

Syntax

CEIL[ING] (number)

Table 1. `CEIL[ING]` Function Parameters
Parameter	Description
number	An expression of a numeric type

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

String and Binary Functions

`ASCII_CHAR()`

Character from ASCII code

Result type

CHAR(1) CHARACTER SET NONE

Syntax

ASCII_CHAR (code)

Table 1. `ASCII_CHAR` Function Parameter
Parameter	Description
code	An integer within the range from 0 to 255

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 returns a character with ASCII code 0 (character NUL) here.

`HEX_DECODE()`

Decode a hexadecimal string to binary

Result type

VARBINARY or BLOB

Syntax

HEX_DECODE (hex_data)

Table 1. `HEX_DECODE` Function Parameter
Parameter	Description
hex_data	Hex encoded data

HEX_DECODE decodes a string with hex-encoded data, and returns the decoded value as VARBINARY or BLOB as appropriate for the input.If the length of the type of hex_data is not a multiple of 2, an error is raised at prepare time.If the length of the value of hex_data is not a multiple of 2, an error is raised at execution time.

When the input is not BLOB, the length of the resulting type is calculated as type_length / 2, where type_length is the maximum length in characters of the input type.

Example of `HEX_DECODE`

select cast(hex_decode('48657861646563696D616C') as varchar(12))
from rdb$database;

CAST
============
Hexadecimal

`HEX_ENCODE()`

Encodes a (binary) value to a hexadecimal string

Result type

VARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII

Syntax

HEX_ENCODE (binary_data)

Table 1. `HEX_ENCODE` Function Parameter
Parameter	Description
binary_data	Binary data (or otherwise convertible to binary) to encode

HEX_ENCODE encodes binary_data with hex, and returns the encoded value as a VARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII as appropriate for the input.

When the input is not BLOB, the length of the resulting type is calculated as type_length * 2, where type_length is the maximum length in bytes of the input type.If this length exceeds the maximum length of VARCHAR, the function returns a BLOB.

Example of `HEX_ENCODE`

select hex_encode('Hexadecimal')
from rdb$database;

HEX_ENCODE
======================
48657861646563696D616C

`LEFT()`

Extracts the leftmost part of a string

Result type

VARCHAR or BLOB

Syntax

LEFT (string, length)

Table 1. `LEFT` Function Parameters
Parameter	Description
string	An expression of a string type
length	Integer expression.The number of characters to return

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.

See also

[fblangref50-scalarfuncs-right]

`LOWER()`

Converts a string to lowercase

Result type

(VAR)CHAR, (VAR)BINARY or BLOB

Syntax

LOWER (string)

Table 1. `LOWER` Function ParameterS
Parameter	Description
string	An expression of a string type

Returns the lowercase 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 character set OCTETS/(VAR)BINARY, the entire string is returned unchanged.

`LOWER` Examples

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

See also

[fblangref50-scalarfuncs-upper]

`LPAD()`

Left-pads a string

Result type

VARCHAR or BLOB

Syntax

LPAD (str, endlen [, padstr])

Table 1. `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 (“`' '`”)

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 equal to '' (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.

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.

`LPAD` 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'

See also

[fblangref50-scalarfuncs-rpad]

`OCTET_LENGTH()`

Length in bytes (octets) of a string or binary value

Result type

INTEGER, or BIGINT for BLOB

Syntax

OCTET_LENGTH (string)

Table 1. `OCTET_LENGTH` Function Parameter
Parameter	Description
string	An expression of a string type

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 or BINARY, this function takes the entire formal string length (i.e. 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.

`OCTET_LENGTH` 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()`

Overwrites part of, or inserts into, a string

Result type

VARCHAR or BLOB

Syntax

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

Table 1. `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

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

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.

`OVERLAY` 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'

See also

[fblangref50-scalarfuncs-replace]

`POSITION()`

Finds the position of the first or next occurrence of a substring in a string

Result type

INTEGER

Syntax

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

Table 1. `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

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.

Note

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.
This function fully supports text BLOBs of any size and character set.

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` 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

See also

[fblangref50-scalarfuncs-substring]

`REPLACE()`

Replaces all occurrences of a substring in a string

Result type

VARCHAR or BLOB

Syntax

REPLACE (str, find, repl)

Table 1. `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

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.

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` 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'

`REVERSE()`

Reverses a string

Result type

VARCHAR

Syntax

REVERSE (string)

Table 1. `REVERSE` Function Parameter
Parameter	Description
string	An expression of a string type

`REVERSE` Examples

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

Tip	This function is useful 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');`

`ASCII_VAL()`

ASCII code from string

Result type

SMALLINT

Syntax

ASCII_VAL (ch)

Table 1. `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

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.

`RIGHT()`

Extracts the rightmost part of a string

Result type

VARCHAR or BLOB

Syntax

RIGHT (string, length)

Table 1. `RIGHT` Function Parameters
Parameter	Description
string	An expression of a string type
length	Integer.The number of characters to return

This function supports text BLOBs of any length.
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()`

Right-pads a string

Result type

VARCHAR or BLOB

Syntax

RPAD (str, endlen [, padstr])

Table 1. `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 (`' '`)

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.

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.

`RPAD` 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'

See also

[fblangref50-scalarfuncs-lpad]

`SUBSTRING()`

Extracts a substring by position and length, or by SQL regular expression

Result types

VARCHAR or BLOB

Syntax

SUBSTRING ( <substring-args> )

<substring-args> ::=
    str FROM startpos [FOR length]
  | str SIMILAR <similar-pattern> ESCAPE <escape>

<similar-pattern> ::=
  <similar-pattern-R1>
  <escape> " <similar-pattern-R2> <escape> "
  <similar-pattern-R3>

Table 1. `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
similar-pattern	SQL regular expression pattern to search for the substring
escape	Escape character

Returns a string’s substring starting at the given position, either to the end of the string or with a given length, or extracts a substring using an SQL regular expression pattern.

If any argument is NULL, the result is also NULL.

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.

Positional `SUBSTRING`

In its simple, positional form (with FROM), this function returns the substring starting at character position startpos (the first character 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.

When startpos is smaller than 1, substring behaves as if the string has 1 - startpos extra positions before the actual first character at position 1.The length is considered from this imaginary start of the string, so the resulting string could be shorter than the specified length, or even empty.

The function fully supports binary and text BLOBs of any length, and with any character set.If str is a BLOB, the result is also a BLOB.For any other argument type, the result is a VARCHAR.

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'.

Example

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

select substring('abcdef' from 1 for 2) from rdb$database;
-- result: 'ab'

select substring('abcdef' from 2) from rdb$database;
-- result: 'bcdef'

select substring('abcdef' from 0 for 2) from rdb$database;
-- result: 'a'
-- and NOT 'ab', because there is "nothing" at position 0

select substring('abcdef' from -5 for 2) from rdb$database;
-- result: ''
-- length ends before the actual start of the string

Regular Expression `SUBSTRING`

In the regular expression form (with SIMILAR), the SUBSTRING function returns part of the string matching an SQL regular expression pattern.If no match is found, NULL is returned.

The SIMILAR pattern is formed from three SQL regular expression patterns, R1, R2 and R3.The entire pattern takes the form of R1 || '<escape>"' || R2 || '<escape>"' || R3, where <escape> is the escape character defined in the ESCAPE clause.R2 is the pattern that matches the substring to extract, and is enclosed between escaped double quotes (<escape>", e.g. “#"” with escape character ‘#’).R1 matches the prefix of the string, and R3 the suffix of the string.Both R1 and R3 are optional (they can be empty), but the pattern must match the entire string.In other words, it is not sufficient to specify a pattern that only finds the substring to extract.

Tip	The escaped double quotes around R2 can be compared to defining a single capture group in more common regular expression syntax like PCRE.That is, the full pattern is equivalent to `R1(R2)R3`, which must match the entire input string, and the capture group is the substring to be returned.

Note	If any one of R1, R2, or R3 is not a zero-length string and does not have the format of an SQL regular expression, then an exception is raised.

The full SQL regular expression format is described in Syntax: SQL Regular Expressions

Examples

substring('abcabc' similar 'a#"bcab#"c' escape '#')  -- bcab
substring('abcabc' similar 'a#"%#"c' escape '#')     -- bcab
substring('abcabc' similar '_#"%#"_' escape '#')     -- bcab
substring('abcabc' similar '#"(abc)*#"' escape '#')  -- abcabc
substring('abcabc' similar '#"abc#"' escape '#')     -- <null>

`TRIM()`

Trims leading and/or trailing spaces or other substrings from a string

Result type

VARCHAR or BLOB

Syntax

TRIM ([<adjust>] str)

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

<where> ::=  BOTH | LEADING | TRAILING

Table 1. `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, or both sides of the input string str.By default, it is space (`' '`)

Removes leading and/or trailing spaces (or optionally other strings) from the input string.

Note	If str is a `BLOB`, the result is a `BLOB`.Otherwise, it is a `VARCHAR(n)` with n the formal length of str.

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.

Built-In Scalar Functions

Built-in Scalar Functions

Context Functions

docnext count = 100

RDB$GET_CONTEXT()

The SYSTEM Namespace

The DDL_TRIGGER Namespace

Examples

RDB$SET_CONTEXT()

Special Functions for DECFLOAT

COMPARE_DECFLOAT()

NORMALIZE_DECFLOAT()

Examples of NORMALIZE_DECFLOAT

QUANTIZE()

Examples of QUANTIZE

TOTALORDER()

Cryptographic Functions

DECRYPT()

DECRYPT Examples

ENCRYPT()

ENCRYPT Examples

RSA_DECRYPT()

RSA_DECRYPT Examples

RSA_ENCRYPT()

RSA_ENCRYPT Examples

RSA_PRIVATE()

RSA_PRIVATE Examples

RSA_PUBLIC()

RSA_PUBLIC Examples

RSA_SIGN_HASH()

RSA_SIGN_HASH Examples

RSA_VERIFY_HASH()

RSA_VERIFY_HASH Examples

Other Functions

MAKE_DBKEY()

Examples of MAKE_DBKEY

RDB$ERROR()

Example of RDB$ERROR

RDB$GET_TRANSACTION_CN()

RDB$GET_TRANSACTION_CN Examples

RDB$ROLE_IN_USE()

RDB$ROLE_IN_USE Examples

RDB$SYSTEM_PRIVILEGE()

RDB$SYSTEM_PRIVILEGE Examples

Mathematical Functions

ABS()

COS()

COSH()

COT()

EXP()

FLOOR()

LN()

LOG()

LOG10()

MOD()

PI()

ACOS()

POWER()

RAND()

ROUND()

ROUND Examples

SIGN()

SIN()

SINH()

SQRT()

TAN()

TANH()

TRUNC()

ACOSH()

ASIN()

ASINH()

ATAN()

ATAN2()

ATANH()

CEIL(), CEILING()

String and Binary Functions

ASCII_CHAR()

HEX_DECODE()

Example of HEX_DECODE

HEX_ENCODE()

`RDB$GET_CONTEXT()`

The `SYSTEM` Namespace

The `DDL_TRIGGER` Namespace

`RDB$SET_CONTEXT()`

Special Functions for `DECFLOAT`

`COMPARE_DECFLOAT()`

`NORMALIZE_DECFLOAT()`

Examples of `NORMALIZE_DECFLOAT`

`QUANTIZE()`

Examples of `QUANTIZE`

`TOTALORDER()`

`DECRYPT()`

`DECRYPT` Examples

`ENCRYPT()`

`ENCRYPT` Examples

`RSA_DECRYPT()`

`RSA_DECRYPT` Examples

`RSA_ENCRYPT()`

`RSA_ENCRYPT` Examples

`RSA_PRIVATE()`

`RSA_PRIVATE` Examples

`RSA_PUBLIC()`

`RSA_PUBLIC` Examples

`RSA_SIGN_HASH()`

`RSA_SIGN_HASH` Examples

`RSA_VERIFY_HASH()`

`RSA_VERIFY_HASH` Examples

`MAKE_DBKEY()`

Examples of `MAKE_DBKEY`

`RDB$ERROR()`

`RDB$GET_TRANSACTION_CN()`

`RDB$GET_TRANSACTION_CN` Examples

`RDB$ROLE_IN_USE()`

`RDB$ROLE_IN_USE` Examples

`RDB$SYSTEM_PRIVILEGE()`

`RDB$SYSTEM_PRIVILEGE` Examples

`ABS()`

`COS()`

`COSH()`

`COT()`

`EXP()`

`FLOOR()`

`LN()`

`LOG()`

`LOG10()`

`MOD()`

`PI()`

`ACOS()`

`POWER()`

`RAND()`

`ROUND()`

`ROUND` Examples

`SIGN()`

`SIN()`

`SINH()`

`SQRT()`

`TAN()`

`TANH()`

`TRUNC()`

`ACOSH()`

`ASIN()`

`ASINH()`

`ATAN()`

`ATAN2()`

`ATANH()`

`CEIL()`, `CEILING()`

`ASCII_CHAR()`

`HEX_DECODE()`

Example of `HEX_DECODE`

`HEX_ENCODE()`

Example of `HEX_ENCODE`

`LEFT()`

`LOWER()`

`LOWER` Examples

`LPAD()`

`LPAD` Examples

`OCTET_LENGTH()`

`OCTET_LENGTH` Examples

`OVERLAY()`

`OVERLAY` Examples