The Firebird 5.0 Language Reference | String And Binary Functions

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

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

`UNICODE_CHAR()`

Character from Unicode code point

Result type

CHAR(1) CHARACTER SET UTF8

Syntax

UNICODE_CHAR (code)

Table 1. `UNICODE_CHAR` Function Parameter
Parameter	Description
code	The Unicode code point (range 0…0x10FFFF)

Returns the character corresponding to the Unicode code point passed in the argument.

`UNICODE_VAL()`

Unicode code point from string

Result type

INTEGER

Syntax

UNICODE_VAL (ch)

Table 1. `UNICODE_VAL` Function Parameter
Parameter	Description
ch	A string of the `[VAR]CHAR` data type or a text `BLOB`

Returns the Unicode code point (range 0…0x10FFFF) of the character passed in.

If the argument is a string with more than one character, the Unicode code point of the first character is returned.
If the argument is an empty string, 0 is returned.
If the argument is NULL, NULL is returned.

`UPPER()`

Converts a string to uppercase

Result type

(VAR)CHAR, (VAR)BINARY or BLOB

Syntax

UPPER (str)

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

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

`UPPER` Examples

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

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

See also

[fblangref50-scalarfuncs-lower]

`BASE64_DECODE()`

Decodes a base64 string to binary

Result type

VARBINARY or BLOB

Syntax

BASE64_DECODE (base64_data)

Table 1. `BASE64_DECODE` Function Parameter
Parameter	Description
base64_data	Base64 encoded data, padded with `=` to multiples of 4

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

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

Example of `BASE64_DECODE`

select cast(base64_decode('VGVzdCBiYXNlNjQ=') as varchar(12))
from rdb$database;

CAST
============
Test base64

`BASE64_ENCODE()`

Encodes a (binary) value to a base64 string

Result type

VARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII

Syntax

BASE64_ENCODE (binary_data)

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

BASE64_ENCODE encodes binary_data with base64, and returns the encoded value as a VARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII as appropriate for the input.The returned value is padded with ‘=’ so its length is a multiple of 4.

When the input is not BLOB, the length of the resulting type is calculated as type_length * 4 / 3 rounded up to a multiple of four, 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 `BASE64_ENCODE`

select base64_encode('Test base64')
from rdb$database;

BASE64_ENCODE
================
VGVzdCBiYXNlNjQ=

`BIT_LENGTH()`

String or binary length in bits

Result type

INTEGER, or BIGINT for BLOB

Syntax

BIT_LENGTH (string)

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

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 (i.e. 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`.

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

`BLOB_APPEND()`

Efficient concatenation of blobs

Result type

BLOB

Syntax

BLOB_APPEND(expr1, expr2 [, exprN ... ])

Table 1. `BLOB_APPEND` Function Parameters
Parameter	Description
exprN	An expression of a type convertible to `BLOB`

The BLOB_APPEND function concatenates blobs without creating intermediate BLOBs, avoiding excessive memory consumption and growth of the database file.The BLOB_APPEND function takes two or more arguments and adds them to a BLOB which remains open for further modification by a subsequent BLOB_APPEND call.

The resulting BLOB is left open for writing instead of being closed when the function returns.In other words, the BLOB can be appended as many times as required.The engine marks the BLOB returned by BLOB_APPEND with an internal flag, BLB_close_on_read, and closes it automatically when needed.

The first argument determines the behaviour of the function:

NULL: new, empty BLOB SUB_TYPE TEXT is created, using the connection character set as the character set
permanent BLOB (from a table) or temporary BLOB which was already closed: new BLOB is created with the same subtype and, if subtype is TEXT the same character set, populated with the content of the original BLOB.
temporary unclosed BLOB with the BLB_close_on_read flag (e.g. created by another call to BLOB_APPEND): used as-is, remaining arguments are appended to this BLOB
other data types: a new BLOB SUB_TYPE TEXT is created, populated with the original argument converted to string.If the original value is a character type, its character set is used (for string literals, the connection character set), otherwise the connection character set.

Other arguments can be of any type.The following behavior is defined for them:

NULLs are ignored (behaves as empty string)
BLOBs, if necessary, are transliterated to the character set of the first argument and their contents are appended to the result
other data types are converted to strings (as usual) and appended to the result

The BLOB_APPEND function returns a temporary unclosed BLOB with the BLB_close_on_read flag.If the first argument is such a temporary unclosed BLOB (e.g. created by a previous call to BLOB_APPEND), it will be used as-is, otherwise a new BLOB is created.Thus, a series of operations like blob = BLOB_APPEND (blob, …) will result in the creation of at most one BLOB (unless you try to append a BLOB to itself).This blob will be automatically closed by the engine when the client reads it, assigns it to a table, or uses it in other expressions that require reading the content.

Warning

Important caveats for BLOB_APPEND

The NULL behaviour of BLOB_APPEND is different from normal concatenation (using ||).Occurrence of NULL will behave as if an empty string was used.In other words, NULL is effectively ignored.

In normal concatenation, concatenating with NULL results in NULL.

Note	Testing a blob for `NULL` using the `IS [NOT] NULL` operator does not read it and therefore a temporary blob with the `BLB_close_on_read` flag will not be closed after such a test.

Tip	Use `LIST` or `BLOB_APPEND` functions to concatenate blobs.This reduces memory consumption and disk I/O, and also prevents database growth due to the creation of many temporary blobs when using the concatenation operator.

`BLOB_APPEND` Examples

execute block
returns (b blob sub_type text)
as
begin
  -- creates a new temporary not closed BLOB
  -- and writes the string from the 2nd argument into it
  b = blob_append(null, 'Hello ');

  -- adds two strings to the temporary BLOB without closing it
  b = blob_append(b, 'World', '!');

  -- comparing a BLOB with a string will close it, because the BLOB needs to be read
  if (b = 'Hello World!') then
  begin
  -- ...
  end

  -- creates a temporary closed BLOB by adding a string to it
  b = b || 'Close';

  suspend;
end

`CHAR_LENGTH()`, `CHARACTER_LENGTH()`

String length in characters

Result type

INTEGER, or BIGINT for BLOB

Syntax

  CHAR_LENGTH (string)
| CHARACTER_LENGTH (string)

Table 1. `CHAR[ACTER]_LENGTH` Function Parameter
Parameter	Description
string	An expression of a string type

Gives the length in characters of the input string.

Note

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

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

`CRYPT_HASH()`

Cryptographic hash

Result type

VARBINARY

Syntax

CRYPT_HASH (value USING <hash>)

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

Table 1. `CRYPT_HASH` Function Parameter
Parameter	Description
value	Expression of value of any type;non-string or non-binary types are converted to string
hash	Cryptographic hash algorithm to apply

CRYPT_HASH returns a cryptographic hash calculated from the input argument using the specified algorithm.If the input argument is not a string or binary type, it is converted to string before hashing.

This function returns a VARBINARY with the length depending on the specified algorithm.

Note

The MD5 and SHA1 algorithms are not recommended for security purposes due to known attacks to generate hash collisions.These two algorithms are provided for backward-compatibility only.
When hashing string or binary values, take into account the effects of trailing blanks (spaces or NULs).The value 'ab' in a CHAR(5) (3 trailing spaces) has a different hash than if it is stored in a VARCHAR(5) (no trailing spaces) or CHAR(6) (4 trailing spaces).

To avoid this, make sure you always use a variable length data type, or the same fixed length data type, or normalize values before hashing, for example using TRIM(TRAILING FROM value).

Examples of `CRYPT_HASH`

Hashing x with the SHA512 algorithm

select crypt_hash(x using sha512) from y;

See also

[fblangref50-scalarfuncs-hash]

`HASH()`

Non-cryptographic hash

Result type

INTEGER, BIGINT

Syntax

HASH (value [USING <hash>])

<hash> ::= CRC32

Table 1. `HASH` Function Parameter
Parameter	Description
value	Expression of value of any type;non-string or non-binary types are converted to string
hash	Non-cryptographic hash algorithm to apply

HASH returns a hash value for the input argument.If the input argument is not a string or binary type, it is converted to string before hashing.

The optional USING clause specifies the non-cryptographic hash algorithm to apply.When the USING clause is absent, the legacy PJW algorithm is applied;this is identical to its behaviour in previous Firebird versions.

This function fully supports text BLOBs of any length and character set.

Supported algorithms

not specified: When no algorithm is specified, Firebird applies the 64-bit variant of the non-cryptographic PJW hash function (also known as ELF64).This is a fast algorithm for general purposes (hash tables, etc.), but its collision quality is suboptimal.Other hash functions — specified explicitly in the USING clause, or cryptographic hashes through [fblangref50-scalarfuncs-crypthash] — should be used for more reliable hashing.

The HASH function returns BIGINT for this algorithm
CRC32: With CRC32, Firebird applies the CRC32 algorithm using the polynomial 0x04C11DB7.

The HASH function returns INTEGER for this algorithm.

Examples of `HASH`

Hashing x with the CRC32 algorithm
```
select hash(x using crc32) from y;
```
Hashing x with the legacy PJW algorithm
```
select hash(x) from y;
```

See also

[fblangref50-scalarfuncs-crypthash]

Date and Time Functions

`DATEADD()`

Adds or subtracts datetime units from a datetime value

Result type

DATE, TIME or TIMESTAMP

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 1. `DATEADD` Function Parameters
Parameter	Description
amount	An integer expression of the `SMALLINT`, `INTEGER` or `BIGINT` type.For unit `MILLISECOND`, the type is `NUMERIC(18, 1)`.A negative value is subtracted.
unit	Date/time unit
datetime	An expression of the `DATE`, `TIME` or `TIMESTAMP` type

Adds the specified number of years, months, weeks, days, hours, minutes, seconds or milliseconds to a date/time value.

The result type is determined by the third argument.
With TIMESTAMP and DATE arguments, all units can be used.
With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Examples of `DATEADD`

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

select
  cast(dateadd(-1 * extract(millisecond from ts) millisecond to ts) as varchar(30)) as t,
  extract(millisecond from ts) as ms
from (
  select timestamp '2014-06-09 13:50:17.4971' as ts
  from rdb$database
) a

T                        MS
------------------------ ------
2014-06-09 13:50:17.0000  497.1

`DATEDIFF()`

Difference between two datetime values in a datetime unit

Result type

BIGINT, or NUMERIC(18,1) for MILLISECOND

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

Returns the number of years, months, weeks, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.

DATE and TIMESTAMP arguments can be combined.No other mixes are allowed.
With TIMESTAMP and DATE arguments, all units can be used.
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.

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

Extracts a datetime unit from a datetime value

Result type

SMALLINT or NUMERIC

Syntax

EXTRACT (<part> FROM <datetime>)

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

Table 1. `EXTRACT` Function Parameters
Parameter	Description
part	Date/time unit
datetime	An expression of the `DATE`, `TIME` or `TIMESTAMP` type

Extracts and returns an element from a DATE, TIME or TIMESTAMP expression.

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 1. Types and ranges of `EXTRACT` results
Part	Type	Range	Comment
`YEAR`	`SMALLINT`	1-9999
`MONTH`	`SMALLINT`	1-12
`QUARTER`	`SMALLINT`	1-4
`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
`TIMEZONE_HOUR`	`SMALLINT`	-23 - +23
`TIMEZONE_MINUTE`	`SMALLINT`	-59 - +59

`MILLISECOND`

Extracts the millisecond value from a TIME or TIMESTAMP.The data type returned is NUMERIC(9,1).

Note	If you extract the millisecond from [fblangref50-contextvars-current-time], be aware that this variable defaults to seconds precision, so the result will always be 0.Extract from `CURRENT_TIME(3)` or [fblangref50-contextvars-current-timestamp] to get milliseconds precision.

`WEEK`

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

See also

Data Types for Dates and Times

`FIRST_DAY()`

Returns the first day of a time period containing a datetime value

Result Type

DATE, TIMESTAMP (with or without time zone)

Syntax

FIRST_DAY(OF <period> FROM date_or_timestamp)

<period> ::= YEAR | MONTH | QUARTER | WEEK

Table 1. `FIRST_DAY` Function Parameters
Parameter	Description
date_or_timestamp	Expression of type `DATE`, `TIMESTAMP WITHOUT TIME ZONE` or `TIMESTAMP WITH TIME ZONE`

FIRST_DAY returns a date or timestamp (same as the type of date_or_timestamp) with the first day of the year, month or week of a given date or timestamp value.

Note	The first day of the week is considered as Sunday, following the same rules as for [fblangref50-scalarfuncs-extract] with `WEEKDAY`. When a timestamp is passed, the return value preserves the time part.

Examples of `FIRST_DAY`

select
  first_day(of month from current_date),
  first_day(of year from current_timestamp),
  first_day(of week from date '2017-11-01'),
  first_day(of quarter from date '2017-11-01')
from rdb$database;

`LAST_DAY()`

Returns the last day of a time period containing a datetime value

Result Type

DATE, TIMESTAMP (with or without time zone)

Syntax

LAST_DAY(OF <period> FROM date_or_timestamp)

<period> ::= YEAR | MONTH | QUARTER | WEEK

Table 1. `LAST_DAY` Function Parameters
Parameter	Description
date_or_timestamp	Expression of type `DATE`, `TIMESTAMP WITHOUT TIME ZONE` or `TIMESTAMP WITH TIME ZONE`

LAST_DAY returns a date or timestamp (same as the type of date_or_timestamp) with the last day of the year, month or week of a given date or timestamp value.

Note	The last day of the week is considered as Saturday, following the same rules as for [fblangref50-scalarfuncs-extract] with `WEEKDAY`. When a timestamp is passed, the return value preserves the time part.

Examples of `LAST_DAY`

select
  last_day(of month from current_date),
  last_day(of year from current_timestamp),
  last_day(of week from date '2017-11-01'),
  last_day(of quarter from date '2017-11-01')
from rdb$database;

Type Casting Functions

`CAST()`

Converts a value from one data type to another

Result type

As specified by target_type

Syntax

CAST (<expression> AS <target_type>)

<target_type> ::= <domain_or_non_array_type> | <array_datatype>

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<array_datatype> ::=
  !! See Array Data Types Syntax !!

Table 1. `CAST` Function Parameters
Parameter	Description
expression	SQL expression
sql_datatype	SQL data type

CAST converts an expression to the desired data type 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.In the SQL standard, this feature is called “datetime literals”.

Note	Since Firebird 4.0, the use of `'NOW'`, `'YESTERDAY'` and `'TOMORROW'` in the shorthand cast is no longer allowed;only literals defining a fixed moment in time are supported.

Allowed Type Conversions

The following table shows the type conversions possible with CAST.

Table 1. Possible Type-castings with `CAST`
From	To
Numeric types	Numeric types `[VAR]CHAR` `BLOB`
`[VAR]CHAR` `BLOB`	`[VAR]CHAR` `BLOB` Numeric types `DATE` `TIME` `TIMESTAMP`
`DATE` `TIME`	`[VAR]CHAR` `BLOB` `TIMESTAMP`
`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 Parameters

You can also cast statement parameters to a data type:

cast (? as integer)

This gives you control over the type of the parameter 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

Casting to a domain or its base type are supported.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

It is also 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;

Warning

Warnings

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.

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

However, 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 cast('today' as date) - 7 from rdb$database

Bitwise Functions

`BIN_AND()`

Bitwise AND

Result type

integer type (the widest type of the arguments)

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

Syntax

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

Table 1. `BIN_AND` Function Parameters
Parameter	Description
number	A number of an integer type

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

`BIN_NOT()`

Bitwise NOT

Result type

integer type matching the argument

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

Syntax

BIN_NOT (number)

Table 1. `BIN_NOT` Function Parameter
Parameter	Description
number	A number of an integer type

Returns the result of the bitwise NOT operation on the argument, i.e. one’s complement.

`BIN_OR()`

Bitwise OR

Result type

integer type (the widest type of the arguments)

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

Syntax

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

Table 1. `BIN_OR` Function Parameters
Parameter	Description
number	A number of an integer type

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

`BIN_SHL()`

Bitwise left-shift

Result type

BIGINT or INT128 depending on the first argument

Syntax

BIN_SHL (number, shift)

Table 1. `BIN_SHL` Function Parameters
Parameter	Description
number	A number of an integer type
shift	The number of bits the number value is shifted by

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

See also

[fblangref50-scalarfuncs-bin-shr]

`BIN_SHR()`

Bitwise right-shift with sign extension

Result type

BIGINT or INT128 depending on the first argument

Syntax

BIN_SHR (number, shift)

Table 1. `BIN_SHR` Function Parameters
Parameter	Description
number	A number of an integer type
shift	The number of bits the number value is shifted by

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 (x86 SAR), meaning that the sign of the first operand is always preserved.

See also

[fblangref50-scalarfuncs-bin-shl]

`BIN_XOR()`

Bitwise XOR

Result type

integer type (the widest type of the arguments)

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

Syntax

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

Table 1. `BIN_XOR` Function Parameters
Parameter	Description
number	A number of an integer type

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

UUID Functions

`CHAR_TO_UUID()`

Converts a string UUID to its binary representation

Result type

BINARY(16)

Syntax

CHAR_TO_UUID (ascii_uuid)

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

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

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

Generates a random binary UUID

Result type

BINARY(16)

Syntax

GEN_UUID ()

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

`GEN_UUID` Example

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

`UUID_TO_CHAR()`

Converts a binary UUID to its string representation

Result type

CHAR(36)

Syntax

UUID_TO_CHAR (uuid)

Table 1. `UUID_TO_CHAR` Function Parameters
Parameter	Description
uuid	16-byte UUID

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

`UUID_TO_CHAR` 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 Sequences (Generators)

`GEN_ID()`

Increments a sequence (generator) value and returns its new value

Result type

BIGINT — dialect 2 and 3
INTEGER — dialect 1

Syntax

GEN_ID (generator-name, step)

Table 1. `GEN_ID` Function Parameters
Parameter	Description
generator-name	Identifier name of a generator (sequence)
step	An integer expression of the increment

If step equals 0, the function will leave the value of the generator unchanged and return its current value.

The SQL-compliant NEXT VALUE FOR syntax is preferred, except when an increment other than the configured increment of the sequence is needed.

Warning

If the value of the step parameter is less than zero, it will decrease the value of the generator.You should be cautious with such manipulations in the database, as they could compromise data integrity (meaning, subsequent insert statements could fail due to generating of duplicate id values).

Note	In dialect 1, the result type is `INTEGER`, in dialect 2 and 3 it is `BIGINT`.

`GEN_ID` Example

new.rec_id = gen_id(gen_recnum, 1);

Conditional Functions

`COALESCE()`

Returns the first non-NULL argument

Result type

Depends on input

Syntax

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

Table 1. `COALESCE` Function Parameters
Parameter	Description
exp1, exp2 … expN	A list of expressions of compatible types

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.

`COALESCE` Examples

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.This scheme only works if absent nicknames and first names are NULL: if one of them is an empty string, COALESCE will happily return that to the caller.That problem can be fixed by using [fblangref50-scalarfuncs-nullif].

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

`DECODE()`

Shorthand “simple CASE”-equivalent function

Result type

Depends on input

Syntax

DECODE(<testexpr>,
  <expr1>, <result1>
  [<expr2>, <result2> ...]
  [, <defaultresult>])

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

DECODE is a shorthand 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.

The equivalent CASE construct:

CASE <testexpr>
  WHEN <expr1> THEN <result1>
  [WHEN <expr2> THEN <result2> ...]
  [ELSE <defaultresult>]
END

Caution

Matching is done with the ‘=’ operator, so if testexpr is NULL, it won’t match any of the exprs, not even those that are NULL.

`DECODE` Examples

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

See also

CASE, Simple CASE

`IIF()`

Ternary conditional function

Result type

Depends on input

Syntax

IIF (<condition>, ResultT, ResultF)

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

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 “<condition> ? resultT : resultF” operator in C-like languages.

Note	`IIF(<condition>, resultT, resultF)` is a shorthand for “`CASE WHEN <condition> THEN resultT ELSE resultF END`”.

`IIF` Examples

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

`MAXVALUE()`

Returns the maximum value of its arguments

Result type

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

Syntax

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

Table 1. `MAXVALUE` Function Parameters
Parameter	Description
expr1 … exprN	List of expressions of compatible types

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.

`MAXVALUE` Examples

SELECT MAXVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

See also

[fblangref50-scalarfuncs-minvalue]

`MINVALUE()`

Returns the minimum value of its arguments

Result type

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

Syntax

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

Table 1. `MINVALUE` Function Parameters
Parameter	Description
expr1 … exprN	List of expressions of compatible types

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.

`MINVALUE` Examples

SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

See also

[fblangref50-scalarfuncs-maxvalue]

`NULLIF()`

Conditional NULL function

Result type

Depends on input

Syntax

NULLIF (<exp1>, <exp2>)

Table 1. `NULLIF` Function Parameters
Parameter	Description
exp1	An expression
exp2	Another expression of a data type compatible with exp1

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

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

SUBSTRING()

SUBSTRING()

Positional SUBSTRING

docnext count = 72

Regular Expression SUBSTRING

TRIM()

TRIM Examples

UNICODE_CHAR()

UNICODE_VAL()

UPPER()

UPPER Examples

BASE64_DECODE()

Example of BASE64_DECODE

BASE64_ENCODE()

Example of BASE64_ENCODE

BIT_LENGTH()

BIT_LENGTH Examples

BLOB_APPEND()

BLOB_APPEND Examples

CHAR_LENGTH(), CHARACTER_LENGTH()

CHAR_LENGTH Examples

CRYPT_HASH()

Examples of CRYPT_HASH

HASH()

Examples of HASH

Date and Time Functions

DATEADD()

Examples of DATEADD

DATEDIFF()

DATEDIFF Examples

EXTRACT()

Returned Data Types and Ranges

MILLISECOND

WEEK

FIRST_DAY()

Examples of FIRST_DAY

LAST_DAY()

Examples of LAST_DAY

Type Casting Functions

CAST()

“Shorthand” Syntax

Allowed Type Conversions

Casting Parameters

Casting to a Domain or its Type

Casting to a Column’s Type

Cast Examples

Bitwise Functions

BIN_AND()

BIN_NOT()

BIN_OR()

BIN_SHL()

BIN_SHR()

BIN_XOR()

UUID Functions

CHAR_TO_UUID()

CHAR_TO_UUID Examples

GEN_UUID()

GEN_UUID Example

UUID_TO_CHAR()

UUID_TO_CHAR Examples

Functions for Sequences (Generators)

GEN_ID()

GEN_ID Example

Conditional Functions

COALESCE()

COALESCE Examples

DECODE()

DECODE Examples

IIF()

IIF Examples

MAXVALUE()

MAXVALUE Examples

MINVALUE()

MINVALUE Examples

NULLIF()

NULLIF Example

`SUBSTRING()`

Positional `SUBSTRING`

Regular Expression `SUBSTRING`

`TRIM()`

`TRIM` Examples

`UNICODE_CHAR()`

`UNICODE_VAL()`

`UPPER()`

`UPPER` Examples

`BASE64_DECODE()`

Example of `BASE64_DECODE`

`BASE64_ENCODE()`

Example of `BASE64_ENCODE`

`BIT_LENGTH()`

`BIT_LENGTH` Examples

`BLOB_APPEND()`

`BLOB_APPEND` Examples

`CHAR_LENGTH()`, `CHARACTER_LENGTH()`

`CHAR_LENGTH` Examples

`CRYPT_HASH()`

Examples of `CRYPT_HASH`

`HASH()`

Examples of `HASH`

`DATEADD()`

Examples of `DATEADD`

`DATEDIFF()`

`DATEDIFF` Examples

`EXTRACT()`

`MILLISECOND`

`WEEK`

`FIRST_DAY()`

Examples of `FIRST_DAY`

`LAST_DAY()`

Examples of `LAST_DAY`

`CAST()`

`BIN_AND()`

`BIN_NOT()`

`BIN_OR()`

`BIN_SHL()`

`BIN_SHR()`

`BIN_XOR()`

`CHAR_TO_UUID()`

`CHAR_TO_UUID` Examples

`GEN_UUID()`

`GEN_UUID` Example

`UUID_TO_CHAR()`

`UUID_TO_CHAR` Examples

`GEN_ID()`

`GEN_ID` Example

`COALESCE()`

`COALESCE` Examples

`DECODE()`

`DECODE` Examples

`IIF()`

`IIF` Examples

`MAXVALUE()`

`MAXVALUE` Examples

`MINVALUE()`

`MINVALUE` Examples

`NULLIF()`

`NULLIF` Example