Content

Abstract

The current file based database drivers in OOo 1.0-1.1 (dBase, flat
file, and spreadsheet) dosn't support any SQL functions. Neither string,
date nor numeric functions. In the new version OOo 2.0 these drivers
will support the following functions.

Functional description

String functions

UCASE(str)
UPPER(str)
Returns the string str with all characters changed to uppercase
according to the ascii character set mapping.

LCASE(str)
LOWER(str)
Returns the string str with all characters changed to lowercase
according to the ascii character set mapping.

ASCII(str)
Returns the ASCII code value of the leftmost character of the string
str. Returns 0 if str is the empty string. Returns NULL if str is NULL:

CHAR(N,...)
CHAR() interprets the arguments as integers and returns a string
consisting of the characters given by the ASCII code values of those
integers. NULL values are skipped:

CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments.
Returns NULL if any argument is NULL. May have more than 2 arguments. A
numeric argument is converted to the equivalent string form.

LOCATE(substr,str)
Returns the position of the first occurrence of substring substr in
string str. Returns 0 if substr is not in str.

SUBSTRING(str,pos)
Returns a substring from string str starting at position pos.

SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
Returns a substring len characters long from string str, starting at
position pos. The variant form that uses FROM is SQL-92 syntax.

LTRIM(str)
Returns the string str with leading space characters removed.

INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos
and len characters long replaced by the string newstr.

LEFT(str,len)
Returns the leftmost len characters from the string str.

RIGHT(str,len)
Returns the rightmost len characters from the string str.

Numeric fucntions

ABS(X)
Returns the absolute value of X.

SIGN(X)
Returns the sign of the argument as -1, 0, or 1, depending on whether X
is negative, zero, or positive.

MOD(N,M)
Modulo (like the % operator in C). Returns the remainder of N divided
by M.

FLOOR(X)
Returns the largest integer value not greater than X.

CEILING(X)
Returns the smallest integer value not less than X.

ROUND(X)
ROUND(X,D)
Returns the argument X, rounded to the nearest integer. With two
arguments rounded to a number to D decimals.

EXP(X)
Returns the value of e (the base of natural logarithms) raised to the
power of X.

LN(X)
Returns the natural logarithm of X.

LOG(X)
LOG(B,X)
If called with one parameter, this function returns the natural
logarithm of X. If called with two parameters, this function returns the
logarithm of X for an arbitary base B.

LOG10(X)
Returns the base-10 logarithm of X.

POWER(X,Y)
Returns the value of X raised to the power of Y.

SQRT(X)
Returns the non-negative square root of X.

PI()
Returns the value of PI.

COS(X)
Returns the cosine of X, where X is given in radians.

SIN(X)
Returns the sine of X, where X is given in radians.

TAN(X)
Returns the tangent of X, where X is given in radians.

ACOS(X)
Returns the arc cosine of X, that is, the value whose cosine is X.
Returns NULL if X is not in the range -1 to 1.

ASIN(X)
Returns the arc sine of X, that is, the value whose sine is X. Returns
NULL if X is not in the range -1 to 1.

ATAN(X)
Returns the arc tangent of X, that is, the value whose tangent is X.

ATAN2(Y,X)
Returns the arc tangent of the two variables X and Y. It is similar to
calculating the arc tangent of Y / X, except that the signs of both
arguments are used to determine the quadrant of the result.

DateTime functions

DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31.

DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366.

MONTH(date)
Returns the month for date, in the range 1 to 12.

DAYNAME(date)
Returns the name of the weekday for date.

MONTHNAME(date)
Returns the name of the month for date.

QUARTER(date)
Returns the quarter of the year for date, in the range 1 to 4.

WEEK(date)
WEEK(date,first)
With a single argument, returns the week for date, in the range 0 to 53
(yes, there may be the beginnings of a week 53), for locations where
Sunday is the first day of the week. The two-argument form of WEEK()
allows you to specify whether the week starts on Sunday or Monday and
whether the return value should be in the range 0-53 or 1-52. Here is a
table for how the second argument works:
Value Meaning
0 Week starts on Sunday and return
value is in range 0-53
1 Week starts on Monday and return
value is in range 0-53

YEAR(date)
Returns the year for date.

HOUR(time)
Returns the hour for time, in the range 0 to 23.

MINUTE(time)
Returns the minute for time, in the range 0 to 59.

SECOND(time)
Returns the second for time, in the range 0 to 59.

CURDATE()
Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format,
depending on whether the function is used in a string or numeric context.

CURTIME()
Returns the current time as a value in 'HH:MM:SS' or HHMMSS format,
depending on whether the function is used in a string or numeric context.

NOW()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'
or YYYYMMDDHHMMSS format, depending on whether the function is used in a
string or numeric context.

Apache, the Apache feather logo, and OpenOffice are trademarks of The Apache Software Foundation.
OpenOffice.org and the seagull logo are registered trademarks of The Apache Software Foundation.
Other names appearing on the site may be trademarks of their respective owners.