Available inDSQL, PSQL

Possible name conflictYES → Read details

Syntax

ASCII_CHAR (code)

Result typeCHAR(1) CHARACTER SET NONE

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

Available inDSQL, PSQL

Possible name conflictYES → Read details

Syntax

ASCII_VAL (ch)

Result typeSMALLINT

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

Available inDSQL, PSQL

Syntax

BIT_LENGTH (string)

Result typeINTEGER

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

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

Examples

select bit_length('Hello!') from rdb$database
-- returns 48
 
select bit_length(_iso8859_1 'Grüß di!') from rdb$database
-- returns 64: ü and ß take up one byte each in ISO8859_1
 
select bit_length
  (cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
from rdb$database
-- returns 80: ü and ß take up two bytes each in UTF8
 
select bit_length
  (cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
from rdb$database
-- returns 208: all 24 CHAR positions count, and two of them are 16-bit

See alsoSection 8.3.9, “OCTET_LENGTH()”, Section 8.3.4, “CHAR_LENGTH(), CHARACTER_LENGTH()”

Available inDSQL, PSQL

Syntax

  CHAR_LENGTH (string)
| CHARACTER_LENGTH (string)

Result typeINTEGER

DescriptionGives the length in characters of the input string.

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.3, “BIT_LENGTH()”, Section 8.3.9, “OCTET_LENGTH()”

Available inDSQL, PSQL

Syntax

HASH (string)

Result typeBIGINT

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

Available inDSQL, PSQL

Syntax

LEFT (string, length)

Result typeVARCHAR or BLOB

DescriptionReturns 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.14, “RIGHT()”

Available inDSQL, ESQL, PSQL

Possible name conflictYES → Read details below

Syntax

LOWER (string)

Result type(VAR)CHAR or BLOB

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

Example

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

See alsoSection 8.3.18, “UPPER()”

Available inDSQL, PSQL

Possible name conflictYES → Read details

Syntax

LPAD (str, endlen [, padstr])

Result typeVARCHAR or BLOB

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

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.15, “RPAD()”

Available inDSQL, PSQL

Syntax

OCTET_LENGTH (string)

Result typeINTEGER

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

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

Examples

select octet_length('Hello!') from rdb$database
-- returns 6
 
select octet_length(_iso8859_1 'Grüß di!') from rdb$database
-- returns 8: ü and ß take up one byte each in ISO8859_1
 
select octet_length
  (cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
from rdb$database
-- returns 10: ü and ß take up two bytes each in UTF8
 
select octet_length
  (cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
from rdb$database
-- returns 26: all 24 CHAR positions count, and two of them are 2-byte

See alsoSection 8.3.3, “BIT_LENGTH()”, Section 8.3.4, “CHAR_LENGTH(), CHARACTER_LENGTH()”

Available inDSQL, PSQL

Syntax

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

Result typeVARCHAR or BLOB

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

• This function supports BLOBs of any length.

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

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

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

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

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

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

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

Examples

overlay ('Goodbye' placing 'Hello' from 2)   -- returns 'GHelloe'
overlay ('Goodbye' placing 'Hello' from 5)   -- returns 'GoodHello'
overlay ('Goodbye' placing 'Hello' from 8)   -- returns 'GoodbyeHello'
overlay ('Goodbye' placing 'Hello' from 20)  -- returns 'GoodbyeHello'
 
overlay ('Goodbye' placing 'Hello' from 2 for 0) -- r. 'GHellooodbye'
overlay ('Goodbye' placing 'Hello' from 2 for 3) -- r. 'GHellobye'
overlay ('Goodbye' placing 'Hello' from 2 for 6) -- r. 'GHello'
overlay ('Goodbye' placing 'Hello' from 2 for 9) -- r. 'GHello'
 
overlay ('Goodbye' placing '' from 4)        -- returns 'Goodbye'
overlay ('Goodbye' placing '' from 4 for 3)  -- returns 'Gooe'
overlay ('Goodbye' placing '' from 4 for 20) -- returns 'Goo'
 
overlay ('' placing 'Hello' from 4)          -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 0)    -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 20)   -- returns 'Hello'

See alsoSection 8.3.12, “REPLACE()”

Available inDSQL, PSQL

Syntax

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

Result typeINTEGER

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

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.16, “SUBSTRING()”

Available inDSQL, PSQL

Syntax

REPLACE (str, find, repl)

Result typeVARCHAR or BLOB

DescriptionReplaces all occurrences of a substring in a string.

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

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

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

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

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

Examples

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

See alsoSection 8.3.10, “OVERLAY()”, Section 8.3.16, “SUBSTRING()”, Section 8.3.11, “POSITION()”, Section 8.3.4, “CHAR_LENGTH(), CHARACTER_LENGTH()”

Available inDSQL, PSQL

Syntax

REVERSE (string)

Result typeVARCHAR

DescriptionReturns a string backwards.

Examples

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

Available inDSQL, PSQL

Possible name conflictYES → Read details

Syntax

RIGHT (string, length)

Result typeVARCHAR or BLOB

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

See alsoSection 8.3.6, “LEFT()”, Section 8.3.16, “SUBSTRING()”

Available inDSQL, PSQL

Changed in2.5 (backported to 2.1.4)

Possible name conflictYES → Read details

Syntax

RPAD (str, endlen [, padstr])

Result typeVARCHAR or BLOB

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

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.8, “LPAD()”

Available inDSQL, PSQL

Changed in2.5.1

Syntax

SUBSTRING (str FROM startpos [FOR length])

Result typesVARCHAR or BLOB

DescriptionReturns a string’s substring starting at the given position, either to the end of the string or with a given length.

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

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

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

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

If any argument is NULL, the result is NULL.

Example

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

See alsoSection 8.3.11, “POSITION()”, Section 8.3.6, “LEFT()”, Section 8.3.14, “RIGHT()”, Section 8.3.4, “CHAR_LENGTH(), CHARACTER_LENGTH()”

Available inDSQL, PSQL

Syntax

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

Result typeVARCHAR or BLOB

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

Examples

select trim ('  Waste no space   ') from rdb$database
-- returns 'Waste no space'
 
select trim (leading from '  Waste no space   ') from rdb$database
-- returns 'Waste no space   '
 
select trim (leading '.' from '  Waste no space   ') from rdb$database
-- returns '  Waste no space   '
 
select trim (trailing '!' from 'Help!!!!') from rdb$database
-- returns 'Help'
 
select trim ('la' from 'lalala I love you Ella') from rdb$database
-- returns ' I love you El'
 
select trim ('la' from 'Lalala I love you Ella') from rdb$database
-- returns 'Lalala I love you El'

Available inDSQL, ESQL, PSQL

Syntax

UPPER (str)

Result type(VAR)CHAR or BLOB

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

Examples

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

See alsoSection 8.3.7, “LOWER()”