Some common HSQLDB stored procedures

HSQLDB is the default database of LibreOffice Base.
Here are some of the stored procedures that might come in handy:

Numerical built-in Functions / Stored Procedures
ABS(d)	returns the absolute value of a double value
CEILING(d)	returns the smallest integer that is not less than d
FLOOR(d)	returns the largest integer that is not greater than d
MOD(a,b)	returns a modulo b
POWER(a,b)	returns a raised to the power of b
RAND()	returns a random number x bigger or equal to 0.0 and smaller than 1.0
ROUND(a,b)	rounds a to b digits after the decimal point
SQRT(d)	returns the square root
String built-in Functions / Stored Procedures
CONCAT(str1,str2)	returns str1 + str2
LENGTH(s)	returns the number of characters in s
LOWER(s)	converts s to lower case
REPEAT(s,count)	returns s repeated count times
REPLACE(s,replace,s2)	replaces all occurrences of replace in s with s2
SUBSTRING(s,start[,len])	returns the substring starting at start (1=left) with length len
TRIM( LEADING ... FROM ...)	TRIM([{LEADING | TRAILING | BOTH}] FROM <string expression>)
UPPER(s)	converts s to upper case
Date/Time built-in Functions / Stored Procedures
CURRENT_DATE	returns the current date
CURRENT_TIME	returns the current time
CURRENT_TIMESTAMP	returns the current timestamp
DATEDIFF(string, datetime1, datetime2)	returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values (both the long and short form of the strings can be used): 'ms'='millisecond',  'ss'='second', 'mi'='minute', 'hh'='hour',  'dd'='day',  'mm'='month',  'yy' = 'year'
DAYOFMONTH(date)	returns the day of the month (1-31)
DAYOFWEEK(date)	returns the day of the week (1 means Sunday)
HOUR(time)	return the hour (0-23)
MINUTE(time)	returns the minute (0-59)
MONTH(date)	returns the month (1-12)
SECOND(time)	returns the second (0-59)
YEAR(date)	returns the year
System built-in Functions / Stored Procedures
CAST(term AS type) CONVERT(term,type)	converts exp to another data type
COALESCE(expr1,expr2,expr3,...)	if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on
CASE v1 WHEN...	CASE v1 WHEN v2 THEN v3 [ELSE v4] END when v1 equals v2 return v3 [otherwise v4 or null if there is no ELSE]
CASE WHEN...	CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

Notational Conventions used above

• [A] means A is optional.
• { B | C } means either B or C must be used.
• [{ B | C }] means either B or C may optionally be used, or nothing at all.
• ( and ) are the actual characters '(' and ')' used in statements.
• UPPERCASE words are keywords