Skip to content

Text / String Manipulation

 

SQL can be used to manipulate text, also called strings. Following is a list of functions that can be used to manipulate text. (Click on the function to open the Firebird Reference Manual)

  • ascii_char() - Returns the ASCII character corresponding to the number passed in the argument.
  • coalesce() - 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.
  • iif() - IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.
  • left() - Returns the leftmost part of the argument string. The number of characters is given in the second argument.
  • lower() - Returns the lower-case equivalent of the input string. The exact result depends on the character set.
  • lpad() - Left-pads a string with spaces or with a user-supplied string until a given length is reached.
  • position() - Returns the (1-based) position of the first occurrence of a substring in a host string.
  • replace() - Replaces all occurrences of a substring in a string.
  • right() - Returns the rightmost part of the argument string. The number of characters is given in the second argument.
  • rpad() - Right-pads a string with spaces or with a user-supplied string until a given length is reached.
  • substring() - Returns a string's substring starting at the given position, either to the end of the string or with a given length.
  • trim() - Removes leading and/or trailing spaces (or optionally other strings) from the input string.
  • upper() - Returns the upper-case equivalent of the input string. The exact result depends on the character set.

Using Firebird as a Calculator

You do not need to use tables and fields in order to perform calculations in Firebird. You can run a calculation using the select [CALCULATION] from RDB$DATABASE format as in the example below.

select 
  
upper('i love quickeasy'
  
from RDB$DATABASE

Notes

  • In Firebird SQL, text is delimited using single quotes - eg. 'this is text'
  • Strings can be joined (concatenated) using ||. eg. 'firstname'||', '||'surname'
  • A line break (carriage return) can be inserted into a text string using: ascii_char(13)||ascii_char(10)
  • If there is a null value in the calculation/concatenation, the result will be null.

Example - Creating an error message based on Transaction values

select
case
  when ((ERROR1 is null) and (ERROR2 is null)) then 'Everything OK'
  when ((ERROR1 is not null) and (ERROR2 is null)) then ERROR1
  when ((ERROR1 is null) and (ERROR2 is not null)) then ERROR2
  when ((ERROR1 is not null) and (ERROR2 is not null)) then ERROR1||ascii_char(13)||ascii_char(10)||ERROR2
end as ERRORMESSAGE
from
  (select
  cast(iif(COMPANYID is null, 'Customer not selected', null) as blob) as ERROR1,
  cast(iif(CONTACTID is null, 'Contact not selected', null) as blob) as ERROR2
  from
  (select
  L.TRANSID,
  L.DOCID,
  L.TRANSDATE,
  L.COMPANYID,
  L.CONTACTID
  from LEDGER L
  where L.TRANSID = 99)
  )