Reference: Use of INTERBASE/Firebird built-in functions

Add * to the function added by fb2.0
Sorting: jianlei 2006-10-13

1. Count, AVG, Max, Min, Sum
Note: general statistical functions are not described in detail.

2. Extract (timestamp_part from value)
Note: extract (Year/Monthe/day/weekday from field name)
Separate the year, month, day, and day of the week from the date field.

3. Cast (value as datatype)
Description: converts data types.

4. Lower ()*
Description: lowercase values are returned.

5. Upper ()
Description: returns an uppercase value.

6. Trim ()*
Remove spaces on both sides of the string

7. substring (string from POS for length)
Note: The position of the first character is 1.

8. bit_length *
Description: returns the number of bits.

9. char_length/character_length *
Description: returns the number of string characters.

10. octet_length *
Description: returns the number of bytes of a string.

11. Case
Description: return values are obtained by executing a set of external conditions.
Example
I) Simple
Select O. ID, O. description,
Case O. Status
When 1 then 'firmed'
When 2 then 'in production'
When 3 then 'ready'
When 4 then 'shipped'
Else 'unknown status' | O. Status | ''''
End
From orders O;
Ii) Expression
Select O. ID, O. description,
Case
When (O. Status is null) Then 'new'
When (O. Status = 1) Then 'confirmed'
When (O. Status = 3) Then 'in production'
When (O. Status = 4) Then 'ready'
When (O. Status = 5) Then 'shipped'
Else 'unknown status' | O. Status | ''''
End
From orders O;

12. IIF (<search_condition>, <value1>, <value2>)
Note: If the expression is true, value1 is returned. Otherwise, value2 is returned.
It is equivalent to the following statement:
Case
When <search_condition> then <value1>
Else <value2>
End

13. nullif (V1, V2)
Note: If V1 = V2, null is returned; otherwise, V1 is returned.
It is equivalent to the following statement:
Case when V1 = v2 then null else V1 end
Example
Update Products
Set stock = nullif (stock, 0)

14. coalesce (V1, V2 ,..., VN)
NOTE: If V1 is null, V2 is returned. Otherwise, V1 is returned,
If n> = 3, it is equal to the following case statement:
Case
When V1 is not null then V1
Else coalesce (V2,..., vn)
End

Example
Select
Proj_name as projectname,
Coalesce (E. full_name, '[<not assigned>]') as employeename
From
Project P
Left join employee e
On (E. emp_no = P. team_leader );

Select
Coalesce (phone, mobilephone, 'unknown ') as "phonenumber"
From relations;