Builtin functions and Stored Procedures
Contents
Overview
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries.
The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL
The component supports two views:
 GUI Query Designer (Designer View)
 Text editor (SQL View)
Queries created and executed in Designer and SQL View support the use of named replaceable parameters in these statements, in most cases. Use of this feature is controlled by the option 'Escape Processing'.
Escape Processing is always ENABLED when using Designer View.
Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button.
The SQL window in Base (opened with the menu commands TOOLS > SQL) allows any valid SQL statement to be executed, but it does not return any data results to the user. Statements executed in this window serve two main purposes.
 Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT.
 Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc.
Multiple statements may be executed as a batch by ending each statement with a semicolon.
All functions listed below are available for use in the SQL window.
Each table below is formatted as follows:
 Function Name  this is the actual function, along with required or optional parameters
 Designer  Yes in this column means it may be used in Designer View. No, the function must be used in SQL View.
 Esc Proc  Yes in this column means it may be used in SQL View, with escape processing ENABLED. No, the function is only available in SQL View with escape processing DISABLED.
 Comments  A brief description of what the function does, the parameters expected and the type of output generated.
Numerical Functions
Function Name  Desinger  Esc Proc  Comments 

ABS(d)  YES  YES  Returns the absolute value of a number. ABS( 1 ) = ABS( 1 ) 
ACOS(d)  YES  YES  Returns the arc cosine of an angle, in the range of 0.0 through pi. ACOS( .5 ) = 1.05 
ASIN(d)  YES  YES  Returns the arc sine of an angle, in the range of pi/2 through pi/2. ASIN( .5 ) = 0.52 
ATAN(d)  YES  YES  Returns the arc tangent of an angle, in the range of pi/2 through pi/2. ATAN( .5 ) = 0.46 
ATAN2(a,b)  YES  YES  Returns the tangent of a/b. ATAN2( 1, 2 ) = 0.46 
BITAND(a,b)  YES  YES  Returns a & b. BITAND( 0, 1 ) = 0 
BITOR(a,b)  YES  YES  Returns a  b BITOR( 0,1 ) = 1 
CEILING(d)  YES  YES  Rreturns the smallest integer that is not less than d CEILING( 8.7 ) = 9 
COS(d)  YES  YES  Returns the trigonometric cosine of an angle. COS( .5 ) = 0.88 
COT(d)  YES  YES  Returns the cotangent of an angle. COT( .5 ) = 1.83 
DEGREES(d)  YES  YES  Converts radians to degrees. DEGREES( 1 ) = 57.29577951 
EXP(d)  YES  YES  Returns the exponential number e (i.e., 2.718...) raised to the power of a double value. EXP( 2 ) = 7.389056 
FLOOR(d)  YES  YES  Returns the largest double value that is not greater than the d. FLOOR( 8.7 ) = 9 
LOG(d)  NO  NO  Returns the natural logarithm (base e) of d. d must be greater than 0.0. LOG( 1.5 ) = 0.41 LOG( 1 ) = 1.#NAN SEE ISSUE#69054 
LOG10(d)  YES  YES  Returns the logarithm (base 10) of d. d must be greater than 0.0. LOG10( 1.5 ) = 0.18 LOG10( 1 ) = 1.#NAN 
MOD(a,b)  YES  YES  Returns a modulo b MOD( 7, 2 ) = 1 
PI()  YES  YES  The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter. PI() = 3.14159265 
POWER(a,b)  YES  YES  Returns of value of the first argument, a, raised to the power of the second argument, b. POWER( 2, 2 ) = 4 
RADIANS(d)  YES  YES  Converts degrees to radians RADIANS( 1 ) = 0.01745329 
RAND()  YES  YES  Returns a random number x >= 0.0 and < 1.0 RAND() MIGHT EQUAL 0.34 
ROUND(a,b)  YES  YES  Rounds a to b digits after the decimal point. ROUND( 1.0333, 2 ) = 1.0300 
ROUNDMAGIC(d)  YES*  YES*  solves certain rounding problems, specifically when numbers approach 0.0 such as 3.113.10.001 ROUNDMAGIC( 3.113.10.001 ) = 0.01 * Currently when used with escape processing enabled the return value is changed to a string. The workaround for this is to cast the result back to a dboule. CAST( ROUNDMAGIC( D ) AS DOUBLE ) See Issue#81031 
SIGN( d )  YES  YES  Returns 1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0 SIGN( 0.001 ) = 1 
SIN(d)  YES  YES  Returns the trigonometric sine of an angle. d is an angle in radians SIN( PI() / 12 ) = 0.25881905 
SQRT( d )  YES  YES  Returns the square root of d. <br. SQRT( 9 ) = 3 
TAN( A )  YES  YES  Returns the trigonometric tangent of an angle, A. A  and angle in in radians. TAN( 0.01745329 * 20 ) = 0.36397018 
TRUNCATE(a,b)  YES  YES  Truncates a to b digits after the decimal point. TRUNCATE( 1.0333, 3 ) = 1.0330 
String Fnctions
Function Name  Desinger  Esc Proc  Comments 

ASCII(s)  YES  YES  Returns the ASCII code of the leftmost character of s ASCII( 'ONE' ) = 79 
BIT_LENGTH(str)  YES  YES  Returns the length of the string in bits BIT_LENGTH ( 'ONE' ) = 48 Each character is 16 bits long on a 32bit system 
CHAR(c)  YES  YES  Returns a character that has the ASCII code c CHAR( 79 ) = O 
CHAR_LENGTH(str)  YES  YES  Returns the length of the string in characters CHAR_LENGTH('ONE') = 3 
CONCAT(str1,str2)  YES  YES  Returns str1 + str2 CONCAT( 'ONE', 'HUNDRED' ) = ONEHUNDRED 
DIFFERENCE(s1,s2)  YES  YES  Returns the difference between the sound of s1 and s2 DIFFERENCE( 'SMITH', 'SMYTH' ) = 0 ; DIFFERENCE( 'WILD', 'CHILD' ) = 1 ; DIFFERENCE( 'TUPLE', 'SUPPLE' ) = 1 ; DIFFERENCE( 'BRAKE', 'BIKE' ) = 2 
HEXTORAW(s1)  YES  YES  Returns translated string ; s1 is string representing character values as 4 digit hex values . '00FF', '00ff00ff' is acceptable ; '0x00FF', 'FF' is NOT and will return null HEXTORAW( '0041' ) = A ; HEXTORAW( 'FF41' ) = a ; HEXTORAW( '00410061' ) = Aa 
INSERT(s,start,len,s2)  YES  YES  Returns a string where len number of characters beginning at start has been replaced by s2 INSERT( 'How brown cow.', 5, 1, 'now ' ) = How now brown cow. 
LCASE(s)  YES  YES  Converts s to lower case LCASE( 'ONE' ) = one 
LEFT(s,count)  YES  YES  Returns the leftmost count of characters of s  requires double quoting  use SUBSTRING() instead LEFT( 'ONE', 2 ) = ON 
LENGTH(s)  YES  YES  Returns the number of characters in s LENGTH( 'ONE' ) = 3 
LOCATE(search,s,[start])  YES  YES  Returns the first index (1=left, 0=not found) where search is found in s, starting at start LOCATE( 'ME', 'FIND ME IF YOU CAN', 1 ) = 6 ; LOCATE( 'ME', 'FIND ME IF YOU CAN', 7 ) = 0 
LTRIM(s)  YES  YES  Removes all leading blanks in s LTRIM( ' ONE ' ) = "ONE " 
LOWER(s)  YES  YES  Converts s to lower case LOWER( 'ONE' ) = one 
OCTET_LENGTH(str)  YES  YES  Returns the length of the string in bytes (twice the number of characters) OCTET_LENGTH( 'ONE' ) = 6 
RAWTOHEX(s1)  YES  YES  Returns translated string, where s1 is any character string and returns the string representation of the characters as hex values RAWTOHEX( 'A' ) = 0041 ; RAWTOHEX( 'Aa' ) = 00410061 
POSITION (<string expression> IN <string expression 1>)  YES  YES  If the first string is a substring of the second one, returns the position of the substring, counting from one; otherwise 0 POSITION ( 'THIS' IN 'THIS AND THAT' ) = 1 
REPEAT(s,count)  YES  YES  Returns s repeated count times REPEAT( 'X', 4 ) = XXXX 
REPLACE(s,replace,s2)  YES  YES  Replaces all occurrences of replace in s with s2 REPLACE( 'WHAT XXXX BROWN COW.', 'XXXX', 'NOW' ) = WHAT NOW BROWN COW. 
RIGHT(s,count)  YES  YES  Returns the rightmost count of characters of s RIGHT( 'THIS AND THAT', 4 ) = THAT 
RTRIM(s)  YES  YES  Removes all trailing spaces RTRIM( ' ONE ' ) = " ONE" 
SOUNDEX(s)  YES  YES  Returns a four character code representing the sound of s SOUNDEX( 'SMITH' ) = S530 ; SOUNDEX( 'SMYTH' ) = S530 
SPACE(count)  YES  YES  returns a string consisting of count spaces SPACE( 4 ) = " " 
SUBSTR(s,start[,len])  YES  YES  alias for substring SUBSTR( 'HERE I AM', 6, 1 ) 
SUBSTRING(s,start[,len])  YES  YES  returns the substring starting at start (1=left) with length len SUBSTR( 'HERE I AM', 6, 1 ) = I ; SUBSTR( 'HERE I AM', 6 ) = I AM 
SUBSTRING( s FROM start [FOR len] )  YES  YES  Alternate syntax where s may be a string expression SUBSTRING ( 'HERE I AM' FROM 6 FOR 1 ) = I ; SUBSTRING ( 'HERE I AM' FROM 6 ) = I AM 
TRIM( [ LEADING  TRAILING  BOTH ] FROM s )  YES*  YES*  Removes leading or trailing spaces from s or both TRIM( LEADING FROM ' ONE ' ) = "ONE " ; TRIM ( TRAILING FROM ' ONE ' ) = "ONE " ; TRIM ( BOTH FROM ' ONE ' ) = "ONE" ; * note  with escape processing DISABLED the command may be abbreviated to TRIM ( FROM ' ONE ' ) AS "OUTPUT" = "ONE"  An alias must be used in this case also 
UCASE(s)  YES  YES  Converts s to upper case UCASE( 'one' ) = ONE 
UPPER(s)  YES  YES  Converts s to upper case UPPER( 'one' ) = ONE 
Date and Time Functions
Function Name  Desinger  Esc Proc  Comments  

CURDATE()  YES  YES  Returns the current date. This is the current system date on your PC. CURDATE() = 09/01/07 NOTE  The parenthesize are required.  
CURTIME()  YES  YES  Returns the current time. This is the current system time on your PC. CURTIME() = 10:16:04 PM NOTE  The parenthesize are required.  
DATEDIFF(string, datetime1, datetime2)  YES  YES  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 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used. DATEDIFF('dd', '20070801', '20070901' ) = 31  
DAYNAME(date)  YES  YES  Returns the name of the day of the week. DAYNAME( '20070901' ) = Saturday  
DAYOFMONTH(date)  YES  YES  Returns the day of the month (131) DAYOFMONTH( '20070901' ) = 1  
DAYOFWEEK(date)  YES  YES  Returns the day of the week (1 means Sunday) DAYOFWEEK( '20070901' ) = 7  
DAYOFYEAR(date)  YES  YES  Returns the day of the year (1366) DAYOFYEAR( '20070901' ) = 244  
HOUR(time)  YES  YES  Return the hour (023) HOUR( '21:16:04' )  
MINUTE(time)  YES  YES  Returns the minute (059) MINUTE( '21:16:04' ) = 16  
MONTH(date)  YES  YES  Returns the month (112) MONTH( '20070901' ) = 9  
MONTHNAME(date)  YES  YES  Returns the name of the month, MONTHNAME( '20070901' ) = September  
NOW()  YES  YES  Returns the current date and time as a timestamp) use of CURRENT_TIMESTAMP instead is suggested NOW() = 09/01/07 10:34 PM  
QUARTER(date)  YES  YES  Returns the quarter (14), with the new year starting in January QUARTER( '20070901' ) = 3  
SECOND(time)  YES  YES  Returns the second (059) SECOND( CURRENT_TIME ) MAY EQUAL 6  
WEEK(date)  YES  YES  Returns the week of this year (153) WEEK( '20070901' ) = 35  
YEAR(date)  YES  YES  Returns the year YEAR( '20070901' ) = 2007  
CURRENT_DATE  YES  YES  Returns the current date CURRENT_DATE = 09/01/07  
CURRENT_TIME  YES  YES  Returns the current time CURRENT_TIME = 10:44:28 PM  
CURRENT_TIMESTAMP  YES  YES  Returns the current timestamp CURRENT_TIMESTAMP = 09/01/07 10:34 PM  
TO_CHAR( datetime, format String )  YES  YES  Returns a string from a date or datetime, based on the format mask Following is a list of valid format mask character sequences,

System Functions
Function Name  Desinger  Esc Proc  Comments 

DATABASE()  YES  YES  Returns the fully qualified file name of the Base file DATABASE() = C:\Documents and Settings\HP_Owner\My Documents\Forum\ChucrchLibrary\new_library.odb 
USER()  NO  NO  Returns the name of the user connected to the Base database NOTE  This command requires use of the parentheses. USER() = SA 
CURRENT_USER  YES  YES  Returns the name of the user connected to the Base database. This is actually a macro that calls the function USER(). NOTE  Use of parentheses is not allowed with this macro. CURRENT_USER = SA 
IDENTITY()  YES  YES  Returns the last identity value that was inserted by this connection. 
Aggregate Functions
Function Name  Desinger  Esc Proc  Comments 

COUNT( any  * )  YES  YES  
MIN(d)  YES  YES  
MAX(d)  YES  YES  
SUM(d)  YES  YES  
AVG(d)  YES  YES  
SOME(b)  NO  NO  
EVERY(b)  YES  YES 
Statistical Functions
Function Name  Desinger  Esc Proc  Comments 

VAR_POP  
VAR_SAMP  
STDDEV_POP  
STDDEV_SAMP 