| <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> |
| <html> |
| <head> |
| <meta http-equiv="content-type" |
| content="text/html; charset=ISO-8859-1"> |
| <title>SQL functions for file based database drivers</title> |
| </head> |
| <body> |
| <div style="text-align: center;"><font size="+3">SQL Functions for file |
| based database drivers<br> |
| </font></div> |
| <div style="text-align: center;"><font size="+1"><span |
| style="font-style: italic; font-weight: bold;">Feature Specification<br> |
| </span></font> |
| <div style="text-align: left;"> |
| <h3><font size="+1">Content</font></h3> |
| <a href="#abstract">Abstract</a><br> |
| <a href="#functional">Functional Description</a><br> |
| <h3><a name="abstract"></a>Abstract</h3> |
| 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.<br> |
| <h3><a name="functional"></a>Functional description</h3> |
| <h1><font face="arial, sans-serif"><font size="2">String functions</font></font></h1> |
| <ul> |
| <li> |
| <p><font face="arial, sans-serif"><font size="2">UCASE(str)<br> |
| UPPER(str)<br> |
| Returns the string str with all characters changed to uppercase |
| according to the ascii </font></font><font size="2"><font |
| face="arial, sans-serif">character set </font></font><font |
| face="arial, sans-serif"><font size="2">mapping.<br> |
| </font></font></p> |
| </li> |
| <li> |
| <p><font size="2"><font face="arial, sans-serif">LCASE(str)<br> |
| LOWER(str)<br> |
| Returns the string str with all characters changed to lowercase |
| according to the ascii character set mapping.<br> |
| </font></font></p> |
| </li> |
| <li> |
| <p><font face="arial, sans-serif"><font size="2">ASCII(str)<br> |
| 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:</font></font></p> |
| </li> |
| <li><font face="arial, sans-serif"><font size="2">LENGTH(str)<br> |
| OCTET_LENGTH(str)<br> |
| CHAR_LENGTH(str)<br> |
| CHARACTER_LENGTH(str)<br> |
| Returns the length of the string str:</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">CHAR(N,...)<br> |
| 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:</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">CONCAT(str1,str2,...)<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LOCATE(substr,str)<br> |
| Returns the position of the first occurrence of substring substr in |
| string str. Returns 0 if substr is not in str.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SUBSTRING(str,pos)<br> |
| Returns a substring from string str starting at position pos.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SUBSTRING(str,pos,len)<br> |
| SUBSTRING(str FROM pos FOR len)<br> |
| Returns a substring len characters long from string str, starting at |
| position pos. The variant form that uses FROM is SQL-92 syntax.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LTRIM(str)<br> |
| Returns the string str with leading space characters removed.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">RTRIM(str)<br> |
| Returns the string str with trailing space characters removed.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SPACE(N)<br> |
| Returns a string consisting of N space characters.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">REPLACE(str,from_str,to_str)<br> |
| Returns the string str with all occurrences of the string from_str |
| replaced by the string to_str.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">REPEAT(str,count)<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">INSERT(str,pos,len,newstr)<br> |
| Returns the string str, with the substring beginning at position pos |
| and len characters long replaced by the string newstr.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LEFT(str,len)<br> |
| Returns the leftmost len characters from the string str.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">RIGHT(str,len)<br> |
| Returns the rightmost len characters from the string str.</font></font></li> |
| </ul> |
| <h1><font face="arial, sans-serif"><font size="2">Numeric fucntions</font></font></h1> |
| <ul> |
| <li><font face="arial, sans-serif"><font size="2">ABS(X)<br> |
| Returns the absolute value of X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SIGN(X)<br> |
| Returns the sign of the argument as -1, 0, or 1, depending on whether X |
| is negative, zero, or positive.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">MOD(N,M)<br> |
| Modulo (like the % operator in C). Returns the remainder of N divided |
| by M.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">FLOOR(X)<br> |
| Returns the largest integer value not greater than X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">CEILING(X)<br> |
| Returns the smallest integer value not less than X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">ROUND(X)<br> |
| ROUND(X,D)<br> |
| Returns the argument X, rounded to the nearest integer. With two |
| arguments rounded to a number to D decimals.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">EXP(X)<br> |
| Returns the value of e (the base of natural logarithms) raised to the |
| power of X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LN(X)<br> |
| Returns the natural logarithm of X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LOG(X)<br> |
| LOG(B,X)<br> |
| 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.<br> |
| </font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">LOG10(X)<br> |
| Returns the base-10 logarithm of X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">POWER(X,Y)<br> |
| Returns the value of X raised to the power of Y.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SQRT(X)<br> |
| Returns the non-negative square root of X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">PI()<br> |
| Returns the value of PI.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">COS(X)<br> |
| Returns the cosine of X, where X is given in radians.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SIN(X)<br> |
| Returns the sine of X, where X is given in radians.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">TAN(X)<br> |
| Returns the tangent of X, where X is given in radians.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">ACOS(X)<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">ASIN(X)<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">ATAN(X)<br> |
| Returns the arc tangent of X, that is, the value whose tangent is X.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">ATAN2(Y,X)<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">DEGREES(X)<br> |
| Returns the argument X, converted from radians to degrees.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">RADIANS(X)<br> |
| Returns the argument X, converted from degrees to radians.</font></font></li> |
| </ul> |
| <h1><font face="arial, sans-serif"><font size="2">DateTime functions</font></font></h1> |
| <ul> |
| <li><font face="arial, sans-serif"><font size="2">DAYOFWEEK(date)<br> |
| Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = |
| Saturday). These index values correspond to the ODBC standard.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">DAYOFMONTH(date)<br> |
| Returns the day of the month for date, in the range 1 to 31.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">DAYOFYEAR(date)<br> |
| Returns the day of the year for date, in the range 1 to 366.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">MONTH(date)<br> |
| Returns the month for date, in the range 1 to 12.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">DAYNAME(date)<br> |
| Returns the name of the weekday for date.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">MONTHNAME(date)<br> |
| Returns the name of the month for date.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">QUARTER(date)<br> |
| Returns the quarter of the year for date, in the range 1 to 4.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">WEEK(date)<br> |
| WEEK(date,first)<br> |
| 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:<br> |
| |
| Value Meaning<br> |
| |
| 0 Week starts on Sunday and return |
| value is in range 0-53<br> |
| |
| 1 Week starts on Monday and return |
| value is in range 0-53</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">YEAR(date)<br> |
| Returns the year for date.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">HOUR(time)<br> |
| Returns the hour for time, in the range 0 to 23.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">MINUTE(time)<br> |
| Returns the minute for time, in the range 0 to 59.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">SECOND(time)<br> |
| Returns the second for time, in the range 0 to 59.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">CURDATE()<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">CURTIME()<br> |
| 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.</font></font></li> |
| <li><font face="arial, sans-serif"><font size="2">NOW()<br> |
| 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.</font></font></li> |
| </ul> |
| <br> |
| <hr style="width: 100%; height: 1px;"> Author: Ocke.Janssen<br> |
| Last Modified: $Date: 2003/07/08 07:21:39 $<br> |
| <span style="font-style: italic;">Copyright © 2003 OpenOffice.org</span><br> |
| <font size="+1"><span style="font-style: italic; font-weight: bold;"></span></font></div> |
| </div> |
| </body> |
| </html> |
