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