Project tools

How do I...

SQL Functions for file based database drivers

Feature Specification

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:
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Returns the length of the string str:
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.
RTRIM(str)
Returns the string str with trailing space characters removed.
SPACE(N)
Returns a string consisting of N space characters.
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str.
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL.
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.
DEGREES(X)
Returns the argument X, converted from radians to degrees.
RADIANS(X)
Returns the argument X, converted from degrees to radians.

DateTime functions

DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard.
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.