Difference between revisions of "Built-in functions and Stored Procedures"
DrewJensen (Talk | contribs) (→Date and Time Functions) |
DrewJensen (Talk | contribs) (→Date and Time Functions) |
||
Line 280: | Line 280: | ||
!TO_CHAR( datetime, format String ) | !TO_CHAR( datetime, format String ) | ||
| YES || YES || Returns a string from a date or datetime, based on the format mask<br>Following is a list of valid format mask character sequences, | | YES || YES || Returns a string from a date or datetime, based on the format mask<br>Following is a list of valid format mask character sequences, | ||
− | {|cellpadding=" | + | {|cellpadding="5" border="0" |
|+ | |+ | ||
|- | |- |
Revision as of 04:20, 2 September 2007
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 semi-colon.
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.11-3.1-0.001 ROUNDMAGIC( 3.11-3.1-0.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 sub-string of the second one, returns the position of the sub-string, 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', '2007-08-01', '2007-09-01' ) = 31 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYNAME(date) | YES | YES | Returns the name of the day of the week. DAYNAME( '2007-09-01' ) = Saturday | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFMONTH(date) | YES | YES | Returns the day of the month (1-31) DAYOFMONTH( '2007-09-01' ) = 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFWEEK(date) | YES | YES | Returns the day of the week (1 means Sunday) DAYOFWEEK( '2007-09-01' ) = 7 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFYEAR(date) | YES | YES | Returns the day of the year (1-366) DAYOFYEAR( '2007-09-01' ) = 244 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
HOUR(time) | YES | YES | Return the hour (0-23) HOUR( '21:16:04' ) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MINUTE(time) | YES | YES | Returns the minute (0-59) MINUTE( '21:16:04' ) = 16 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MONTH(date) | YES | YES | Returns the month (1-12) MONTH( '2007-09-01' ) = 9 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MONTHNAME(date) | YES | YES | Returns the name of the month, MONTHNAME( '2007-09-01' ) = 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 (1-4), with the new year starting in January QUARTER( '2007-09-01' ) = 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SECOND(time) | YES | YES | Returns the second (0-59) SECOND( CURRENT_TIME ) MAY EQUAL 6 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
WEEK(date) | YES | YES | Returns the week of this year (1-53) WEEK( '2007-09-01' ) = 35 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
YEAR(date) | YES | YES | Returns the year YEAR( '2007-09-01' ) = 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 |