Difference between revisions of "Built-in functions and Stored Procedures"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎String Fnctions)
(→‎String Fnctions)
Line 140: Line 140:
 
!CONCAT(str1,str2)
 
!CONCAT(str1,str2)
 
| YES || YES || Returns str1 + str2 <BR> CONCAT( 'ONE', 'HUNDRED' ) = ONEHUNDRED
 
| YES || YES || Returns str1 + str2 <BR> CONCAT( 'ONE', 'HUNDRED' ) = ONEHUNDRED
−
'
 
 
|-
 
|-
 
!DIFFERENCE(s1,s2)
 
!DIFFERENCE(s1,s2)
Line 146: Line 145:
 
|-
 
|-
 
!HEXTORAW(s1)
 
!HEXTORAW(s1)
−
| YES || YES || Returns translated string ; s1 is string representing character values as 4 digit hex values . <br> '00FF', '00ff00ff' is acceptable ; '0x00FF', 'FF' is NOT and will return null<BR> HEXTORAW( '0041' ) = A ; HEXTORAW( 'FF41' ) = a ; HEXTORAW( '0041FF41' ) = Aa
+
| YES || YES || Returns translated string ; s1 is string representing character values as 4 digit hex values . <br> '00FF', '00ff00ff' is acceptable ; '0x00FF', 'FF' is NOT and will return null<BR> HEXTORAW( '0041' ) = A ; HEXTORAW( 'FF41' ) = a ; HEXTORAW( '00410061' ) = Aa
 
|-
 
|-
 
!INSERT(s,start,len,s2)
 
!INSERT(s,start,len,s2)
−
| YES || YES ||  
+
| YES || YES || Returns a string where len number of characters beginning at start has been replaced by s2 <br> INSERT( 'How brown cow.', 5, 1, 'now ' ) = How now brown cow.
 
|-
 
|-
 
!LCASE(s)
 
!LCASE(s)
−
| YES || YES ||  
+
| YES || YES || Converts s to lower case <br> LCASE( 'ONE' ) = one
 
|-
 
|-
 
!LEFT(s,count)
 
!LEFT(s,count)
−
| YES || YES ||  
+
| YES || YES || Returns the leftmost count of characters of s - requires double quoting - use SUBSTRING() instead <BR> LEFT( 'ONE', 2 ) = ON
 
|-
 
|-
 
!LENGTH(s)
 
!LENGTH(s)
−
| YES || YES ||  
+
| YES || YES || Returns the number of characters in s <br> LENGTH( 'ONE' ) = 3
 
|-
 
|-
 
!LOCATE(search,s,[start])
 
!LOCATE(search,s,[start])
−
| YES || YES ||  
+
| YES || YES || Returns the first index (1=left, 0=not found) where search is found in s, starting at start <BR> LOCATE( 'ME', 'FIND ME IF YOU CAN', 1 ) = 6 ; LOCATE( 'ME', 'FIND ME IF YOU CAN', 7 ) = 0
 
|-
 
|-
 
!LTRIM(s)
 
!LTRIM(s)
−
| YES || YES ||  
+
| YES || YES || Removes all leading blanks in s <BR> LTRIM( ' ONE ' ) = "ONE "
 +
|-
 +
!LOWER(s)
 +
| YES || YES || Converts s to lower case <br> LOWER( 'ONE' ) = one
 
|-
 
|-
 
!OCTET_LENGTH(str)
 
!OCTET_LENGTH(str)
−
| YES || YES ||  
+
| YES || YES || Returns the length of the string in bytes (twice the number of characters) <BR> OCTET_LENGTH( 'ONE' ) = 6
 
|-
 
|-
 
!RAWTOHEX(s1)
 
!RAWTOHEX(s1)
−
| YES || YES ||  
+
| YES || YES || Returns translated string, where s1 is any character string and returns the string representation of the characters as hex values <BR> RAWTOHEX( 'A' ) = 0041 ; RAWTOHEX( 'Aa' ) = 00410061
 
|-
 
|-
−
!POSITION (... IN ..)
+
!POSITION (<string expression> IN <string expression 1>)
−
| YES || YES ||  
+
| 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 <br> POSITION ( 'THIS' IN 'THIS AND THAT' ) = 1
 
|-
 
|-
 
!REPEAT(s,count)
 
!REPEAT(s,count)
−
| YES || YES ||  
+
| YES || YES || Returns s repeated count times <br> REPEAT( 'X', 4 ) = XXXX
 
|-
 
|-
 
!REPLACE(s,replace,s2)
 
!REPLACE(s,replace,s2)
−
| YES || YES ||  
+
| YES || YES || Replaces all occurrences of replace in s with s2 <br> REPLACE( 'WHAT XXXX BROWN COW.', 'XXXX', 'NOW' ) = WHAT NOW BROWN COW.
 
|-
 
|-
 
!RIGHT(s,count)
 
!RIGHT(s,count)
−
| YES || YES ||  
+
| YES || YES || Returns the rightmost count of characters of s <BR> RIGHT( 'THIS AND THAT', 4 ) = THAT
 
|-
 
|-
 
!RTRIM(s)
 
!RTRIM(s)
−
| YES || YES ||  
+
| YES || YES || Removes all trailing spaces <BR> RTRIM( ' ONE ' ) = " ONE"
 
|-
 
|-
 
!SOUNDEX(s)
 
!SOUNDEX(s)
−
| YES || YES ||  
+
| YES || YES || Returns a four character code representing the sound of s <BR> SOUNDEX( 'SMITH' ) = S530 ; SOUNDEX( 'SMYTH' ) = S530
 
|-
 
|-
 
!SPACE(count)
 
!SPACE(count)
−
| YES || YES ||  
+
| YES || YES || returns a string consisting of count spaces <BR> SPACE( 4 ) = "    "
 
|-
 
|-
 
!SUBSTR(s,start[,len])
 
!SUBSTR(s,start[,len])
−
| YES || YES ||  
+
| YES || YES || alias for substring <BR> SUBSTR( 'HERE I AM', 6, 1 )
 
|-
 
|-
 
!SUBSTRING(s,start[,len])
 
!SUBSTRING(s,start[,len])
−
| YES || YES ||  
+
| YES || YES || returns the substring starting at start (1=left) with length len <BR> SUBSTR( 'HERE I AM', 6, 1 ) = I ; SUBSTR( 'HERE I AM', 6 ) = I AM
 
|-
 
|-
−
!SUBSTRING(... FROM ... FOR ...)
+
!SUBSTRING( s FROM start <nowiki> [FOR len] </nowiki>)
−
| YES || YES ||  
+
| YES || YES ||Alternate syntax where s may be a string expression <br> SUBSTRING ( 'HERE I AM' FROM 6 FOR 1 ) = I ; SUBSTRING ( 'HERE I AM' FROM 6  ) = I AM
 
|-
 
|-
 
!TRIM( <nowiki>[ LEADING | TRAILING | BOTH ] </nowiki>  FROM  s )
 
!TRIM( <nowiki>[ LEADING | TRAILING | BOTH ] </nowiki>  FROM  s )
−
| YES || YES ||  
+
| YES<b>*</b> || YES<b>*</b> || Removes leading or trailing spaces from s or both <br> TRIM( LEADING FROM ' ONE ' ) = "ONE " ; TRIM ( TRAILING FROM ' ONE ' ) = "ONE " ; TRIM ( BOTH  FROM ' ONE ' ) = "ONE" ; <BR> <B>* note -</b> 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)
 
!UCASE(s)
−
| YES || YES ||  
+
| YES || YES || Converts s to upper case <BR> UCASE( 'one' ) = ONE
−
|-
+
−
!LOWER(s)
+
−
| YES || YES ||
+
 
|-
 
|-
 
!UPPER(s)
 
!UPPER(s)
−
| YES || YES ||  
+
| YES || YES || Converts s to upper case <BR> UPPER( 'one' ) = ONE
 
|-
 
|-
 
|}
 
|}

Revision as of 23:10, 1 September 2007

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
CURTIME() YES YES
DATEDIFF(string, datetime1, datetime2) YES YES
DAYNAME(date) YES YES
DAYOFMONTH(date) YES YES
DAYOFWEEK(date) YES YES
DAYOFYEAR(date) YES YES
HOUR(time) YES YES
MINUTE(time) YES YES
MONTH(date) YES YES
MONTHNAME(date) YES YES
NOW() YES YES
QUARTER(date) YES YES
SECOND(time) YES YES
WEEK(date) YES YES
YEAR(date) YES YES
CURRENT_DATE YES YES
CURRENT_TIME YES YES
CURRENT_TIMESTAMP YES YES
TO_CHAR( datetime, format String ) YES YES

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
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Built-in_functions_and_Stored_Procedures&oldid=44169"
Personal tools