Character from ASCII code

Result typeCHAR(1) CHARACTER SET NONE

Syntax

  |ASCII_CHAR (code)

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

See alsoSection 8.3.2, “ASCII_VAL()”, Section 8.3.24, “UNICODE_CHAR()”

ASCII code from string

Result typeSMALLINT

Syntax

  |ASCII_VAL (ch)

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.

See alsoSection 8.3.1, “ASCII_CHAR()”, Section 8.3.25, “UNICODE_VAL()”

Decodes a base64 string to binary

Result typeVARBINARY or BLOB

Syntax

  |BASE64_DECODE (base64_data)

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.

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

Encodes a (binary) value to a base64 string

Result typeVARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII

Syntax

  |BASE64_ENCODE (binary_data)

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.

  |select base64_encode('Test base64')
  |from rdb$database;
  | 
  |BASE64_ENCODE
  |================
  |VGVzdCBiYXNlNjQ=

String or binary length in bits

Result typeINTEGER, or BIGINT for BLOB

Syntax

  |BIT_LENGTH (string)

Gives the length in bits of the input string. For multibyte 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.

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.

   |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

Efficient concatenation of blobs

Result typeBLOB

Syntax

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

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:

1. NULL: new, empty BLOB SUB_TYPE TEXT is created, using the connection character set as the character set

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

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

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

1. NULLs are ignored (behaves as empty string)

2. BLOBs, if necessary, are transliterated to the character set of the first argument and their contents are appended to the result

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

   |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

String length in characters

Result typeINTEGER, or BIGINT for BLOB

Syntax

  |  CHAR_LENGTH (string)
  || CHARACTER_LENGTH (string)

Gives the length in characters of the input string.

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.

   |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

Cryptographic hash

Result typeVARBINARY

Syntax

  |CRYPT_HASH (value USING <hash>)
  | 
  |<hash> ::= MD5 | SHA1 | SHA256 | SHA512

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.

  |select crypt_hash(x using sha512) from y;

Non-cryptographic hash

Result typeINTEGER, BIGINT

Syntax

  |HASH (value [USING <hash>])
  | 
  |<hash> ::= CRC32

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.

Decode a hexadecimal string to binary

Result typeVARBINARY or BLOB

Syntax

  |HEX_DECODE (hex_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.

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

Encodes a (binary) value to a hexadecimal string

Result typeVARCHAR CHARACTER SET ASCII or BLOB SUB_TYPE TEXT CHARACTER SET ASCII

Syntax

  |HEX_ENCODE (binary_data)

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.

  |select hex_encode('Hexadecimal')
  |from rdb$database;
  | 
  |HEX_ENCODE
  |======================
  |48657861646563696D616C

Extracts the leftmost part of a string

Result typeVARCHAR or BLOB

Syntax

  |LEFT (string, length)

• 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 alsoSection 8.3.20, “RIGHT()”

Converts a string to lowercase

Result type(VAR)CHAR, (VAR)BINARY or BLOB

Syntax

  |LOWER (string)

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.

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

Left-pads a string

Result typeVARCHAR or BLOB

Syntax

  |LPAD (str, endlen [, padstr])

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.

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

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

Result typeINTEGER, or BIGINT for BLOB

Syntax

  |OCTET_LENGTH (string)

Gives the length in bytes (octets) of the input string. For multibyte 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.

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.

   |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

Overwrites part of, or inserts into, a string

Result typeVARCHAR or BLOB

Syntax

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

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.

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

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

Result typeINTEGER

Syntax

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

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.

  |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

Replaces all occurrences of a substring in a string

Result typeVARCHAR or BLOB

Syntax

  |REPLACE (str, find, repl)

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

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

Reverses a string

Result typeVARCHAR

Syntax

  |REVERSE (string)

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

  |create index ix_people_email on people
  |  computed by (reverse(email));
  | 
  |select * from people
  |  where reverse(email) starting with reverse('.br');

Extracts the rightmost part of a string

Result typeVARCHAR or BLOB

Syntax

  |RIGHT (string, length)

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

See alsoSection 8.3.12, “LEFT()”, Section 8.3.22, “SUBSTRING()”

Right-pads a string

Result typeVARCHAR or BLOB

Syntax

  |RPAD (str, endlen [, padstr])

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.

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

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

Result typesVARCHAR 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>

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.

   |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

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

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

Result typeVARCHAR or BLOB

Syntax

  |TRIM ([<adjust>] str)
  | 
  |<adjust> ::=  {[<where>] [what]} FROM
  | 
  |<where> ::=  BOTH | LEADING | TRAILING

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

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

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

Character from Unicode code point

Result typeCHAR(1) CHARACTER SET UTF8

Syntax

  |UNICODE_CHAR (code)

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

See alsoSection 8.3.25, “UNICODE_VAL()”, Section 8.3.1, “ASCII_CHAR()”

Unicode code point from string

Result typeINTEGER

Syntax

  |UNICODE_VAL (ch)

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.

See alsoSection 8.3.24, “UNICODE_CHAR()”, Section 8.3.2, “ASCII_VAL()”

Converts a string to uppercase

Result type(VAR)CHAR, (VAR)BINARY or BLOB

Syntax

  |UPPER (str)

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.

  |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