8.3 String and Binary Functions

8.3.1 ASCII_CHAR()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeCHAR(1) CHARACTER SET NONE

Syntax

ASCII_CHAR (code)

Table 8.3.1.1 ASCII_CHAR Function Parameter

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

Important

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

8.3.2 ASCII_VAL()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeSMALLINT

Syntax

ASCII_VAL (ch)

Table 8.3.2.1 ASCII_VAL Function Parameter

Returns the ASCII code of the character passed in.

• If the argument is a string with more than one character, the ASCII code of the first character is returned.

• If the argument is an empty string, 0 is returned.

• If the argument is NULL, NULL is returned.

• If the first character of the argument string is multi-byte, an error is raised. (A bug in Firebird 2.1 - 2.1.3 and 2.5.0 causes an error to be raised if any character in the string is multi-byte. This is fixed in versions 2.1.4 and 2.5.1.)

8.3.3 BASE64_DECODE()

Available inDSQL, PSQL

Result typeVARBINARY or BLOB

Syntax

BASE64_DECODE (base64_data)

Table 8.3.3.1 BASE64_DECODE Function Parameter

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.

8.3.3.1 Example of BASE64_DECODE

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

See alsoSection 8.3.4, BASE64_ENCODE(), Section 8.3.9, HEX_DECODE()

8.3.4 BASE64_ENCODE()

Available inDSQL, PSQL

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

Syntax

BASE64_ENCODE (binary_data)

Table 8.3.4.1 BASE64_ENCODE Function Parameter

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.

8.3.4.1 Example of BASE64_ENCODE

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

See alsoSection 8.3.3, BASE64_DECODE(), Section 8.3.10, HEX_ENCODE()

8.3.5 BIT_LENGTH()

Available inDSQL, PSQL

Result typeINTEGER

Syntax

BIT_LENGTH (string)

Table 8.3.5.1 BIT_LENGTH Function Parameter

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.

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

8.3.5.1 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

See alsoSection 8.3.14, OCTET_LENGTH(), Section 8.3.6, CHAR_LENGTH(), CHARACTER_LENGTH()

8.3.6 CHAR_LENGTH(), CHARACTER_LENGTH()

Available inDSQL, PSQL

Result typeINTEGER

Syntax

  CHAR_LENGTH (string)
| CHARACTER_LENGTH (string)

Table 8.3.6.1 CHAR[ACTER]_LENGTH Function Parameter

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.

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

8.3.6.1 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

See alsoSection 8.3.5, BIT_LENGTH(), Section 8.3.14, OCTET_LENGTH()

8.3.7 CRYPT_HASH()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

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

Table 8.3.7.1 CRYPT_HASH Function Parameter

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, it is important to 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).

8.3.7.1 Examples of CRYPT_HASH

Hashing x with the SHA512 algorithm

select crypt_hash(x using sha512) from y;

See alsoSection 8.3.8, HASH()

8.3.8 HASH()

Available inDSQL, PSQL

Result typeINTEGER,BIGINT

Syntax

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

Table 8.3.8.1 HASH Function Parameter

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 very fast algorithm for general purposes (hash tables, etc.), but its collision quality is sub-optimal. Other hash functions — specified explicitly in the USING clause, or cryptographic hashes through Section 8.3.7, CRYPT_HASH() — 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.

8.3.8.1 Examples of HASH

1. Hashing x with the CRC32 algorithm

   select hash(x using crc32) from y;

2. Hashing x with the legacy PJW algorithm

   select hash(x) from y;

See alsoSection 8.3.7, CRYPT_HASH()

8.3.9 HEX_DECODE()

Available inDSQL, PSQL

Result typeVARBINARY or BLOB

Syntax

HEX_DECODE (hex_data)

Table 8.3.9.1 HEX_DECODE Function Parameter

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.

8.3.9.1 Example of HEX_DECODE

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

See alsoSection 8.3.10, HEX_ENCODE(), Section 8.3.3, BASE64_DECODE()

8.3.10 HEX_ENCODE()

Available inDSQL, PSQL

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

Syntax

HEX_ENCODE (binary_data)

Table 8.3.10.1 HEX_ENCODE Function Parameter

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.

8.3.10.1 Example of HEX_ENCODE

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

See alsoSection 8.3.9, HEX_DECODE(), Section 8.3.4, BASE64_ENCODE()

8.3.11 LEFT()

Available inDSQL, PSQL

Result typeVARCHAR or BLOB

Syntax

LEFT (string, length)

Table 8.3.11.1 LEFT Function Parameters

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

• This function fully supports text BLOBs of any length, including those with a multi-byte character set.

• If string is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*n*) with n the length of the input string.

• If the length argument exceeds the string length, the input string is returned unchanged.

• If the length argument is not a whole number, bankers’ rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

See alsoSection 8.3.19, RIGHT()

8.3.12 LOWER()

Available inDSQL, ESQL, PSQL

Possible name conflictYES → Read details below

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

Syntax

LOWER (string)

Table 8.3.12.1 LOWER Function ParameterS

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

Name Clash

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

8.3.12.1 LOWER Examples

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

See alsoSection 8.3.23, UPPER()

8.3.13 LPAD()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeVARCHAR or BLOB

Syntax

LPAD (str, endlen [, padstr])

Table 8.3.13.1 LPAD Function Parameters

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

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

• If str is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*endlen*).

• If padstr is given and equals '' (empty string), no padding takes place.

• If endlen is less than the current string length, the string is truncated to endlen, even if padstr is the empty string.

Note

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

Warning

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

8.3.13.1 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 alsoSection 8.3.20, RPAD()

8.3.14 OCTET_LENGTH()

Available inDSQL, PSQL

Result typeINTEGER

Syntax

OCTET_LENGTH (string)

Table 8.3.14.1 OCTET_LENGTH Function Parameter

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.

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

8.3.14.1 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

See alsoSection 8.3.5, BIT_LENGTH(), Section 8.3.6, CHAR_LENGTH(), CHARACTER_LENGTH()

8.3.15 OVERLAY()

Available inDSQL, PSQL

Result typeVARCHAR or BLOB

Syntax

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

Table 8.3.15.1 OVERLAY Function Parameters

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

• This function supports BLOBs of any length.

• If string or replacement is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*n*) with n the sum of the lengths of string and replacement.

• As usual in SQL string functions, pos is 1-based.

• If pos is beyond the end of string, replacement is placed directly after string.

• If the number of characters from pos to the end of string is smaller than the length of replacement (or than the length argument, if present), string is truncated at pos and replacement placed after it.

• The effect of a FOR 0 clause is that replacement is simply inserted into string.

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

• If pos or length is not a whole number, bankers’ rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

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.

8.3.15.1 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 alsoSection 8.3.17, REPLACE()

8.3.16 POSITION()

Available inDSQL, PSQL

Result typeINTEGER

Syntax

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

Table 8.3.16.1 POSITION Function Parameters

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.

  Notice: A bug in Firebird 2.1 - 2.1.3 and 2.5.0 causes POSITION to always return 1 if substr is the empty string. This is fixed in 2.1.4 and 2.5.1.

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

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.

8.3.16.1 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 alsoSection 8.3.21, SUBSTRING()

8.3.17 REPLACE()

Available inDSQL, PSQL

Result typeVARCHAR or BLOB

Syntax

REPLACE (str, find, repl)

Table 8.3.17.1 REPLACE Function Parameters

Replaces all occurrences of a substring in a string.

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

• If any argument is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*n*) with n calculated from the lengths of str, find and repl in such a way that even the maximum possible number of replacements won’t overflow the field.

• If find is the empty string, str is returned unchanged.

• If repl is the empty string, all occurrences of find are deleted from str.

• If any argument is NULL, the result is always NULL, even if nothing would have been replaced.

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.

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

See alsoSection 8.3.15, OVERLAY(), Section 8.3.21, SUBSTRING(), Section 8.3.16, POSITION(), Section 8.3.6, CHAR_LENGTH(), CHARACTER_LENGTH()

8.3.18 REVERSE()

Available inDSQL, PSQL

Result typeVARCHAR

Syntax

REVERSE (string)

Table 8.3.18.1 REVERSE Function Parameter

Returns a string backwards.

8.3.18.1 REVERSE Examples

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

Tip

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

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

8.3.19 RIGHT()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeVARCHAR or BLOB

Syntax

RIGHT (string, length)

Table 8.3.19.1 RIGHT Function Parameters

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

• This function supports text BLOBs of any length, but has a bug in versions 2.1 - 2.1.3 and 2.5.0 that makes it fail with text BLOBs larger than 1024 bytes that have a multi-byte character set. This has been fixed in versions 2.1.4 and 2.5.1.

• If string is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*n*) with n the length of the input string.

• If the length argument exceeds the string length, the input string is returned unchanged.

• If the length argument is not a whole number, bankers’ rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

Warning

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

See alsoSection 8.3.11, LEFT(), Section 8.3.21, SUBSTRING()

8.3.20 RPAD()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeVARCHAR or BLOB

Syntax

RPAD (str, endlen [, padstr])

Table 8.3.20.1 RPAD Function Parameters

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

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

• If str is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(*endlen*).

• If padstr is given and equals '' (empty string), no padding takes place.

• If endlen is less than the current string length, the string is truncated to endlen, even if padstr is the empty string.

Note

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

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.

8.3.20.1 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 alsoSection 8.3.13, LPAD()

8.3.21 SUBSTRING()

Available inDSQL, PSQL

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>

Table 8.3.21.1 SUBSTRING Function Parameters

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.

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

Since Firebird 4.0, startpos can be smaller than 1. 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

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

See alsoSection 8.3.16, POSITION(), Section 8.3.11, LEFT(), Section 8.3.19, RIGHT(), Section 8.3.6, CHAR_LENGTH(), CHARACTER_LENGTH(), SIMILAR TO

8.3.22 TRIM()

Available inDSQL, PSQL

Result typeVARCHAR or BLOB

Syntax

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

Table 8.3.22.1 TRIM Function Parameters

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

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.

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

8.3.23 UPPER()

Available inDSQL, ESQL, PSQL

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

Syntax

UPPER (str)

Table 8.3.23.1 UPPER Function Parameter

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

8.3.23.1 UPPER Examples

select upper(_iso8859_1 'Débâcle')
from rdb$database
-- returns 'DÉBÂCLE' (before Firebird 2.0: 'DéBâCLE')
 
select upper(_iso8859_1 'Débâcle' collate fr_fr)
from rdb$database
-- returns 'DEBACLE', following French uppercasing rules

See alsoSection 8.3.12, LOWER()