Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 3 SQL Functions

This document discusses SQL functions used with Oracle Database Lite. Topics include:

- Section 3.1, "SQL Function Types"

- Section 3.2, "SQL Functions Overview"

- Section 3.3, "SQL Functions Alphabetical Listing"

## 3.1 SQL Function Types

This section lists the different types of SQL functions.The "SQL Functions Overview" provides an explanation of each function.

SQL Function Types

Number Function | SQL Types
--- | ---
CEIL | ROUND - Number Function
FLOOR | TRUNC
MOD |

Character Function | SQL Types
--- | ---
CHR | ROUND - Date Function
CONCAT | SUBSTR
INITCAP | SUBSTRB
LCASE See LOWER | TRANSLATE
LOWER | TRIM
LPAD | UCASE See UPPER
LTRIM | UPPER
REPLACE | USER
RPAD |

Character Functions | Returning Number Values
--- | ---
ASCII | LENGTH
BIT_LENGTH (See LENGTH) | LENGTHB
CHAR_LENGTH (See LENGTH) | OCTET_LENGTH (See LENGTH)
INSTR | POSITION
INSTRB |

Date | Functions | SQL | Types
--- | --- | --- | ---
ADD_MONTHS | DAYOFMONTH | MONTHNAME | TIMESTAMPADD
CURDATE | DAYOFWEEK | MONTHS_BETWEEN | TIMESTAMPDIFF
CURRENT_DATE | DAYOFYEAR | NEXT_DAY | TRUNC
CURRENT_TIME | HOUR | NOW | WEEK
CURRENT_TIMESTAMP | LAST_DAY | ROUND - Date Function | YEAR
CURTIME | MINUTE | SECOND |
DAYNAME | MONTH | SYSDATE |

Conversion Functions | Other Functions | Grouping Functions
--- | --- | ---
CAST | CASE | AVG
CONVERT | DATABASE | COUNT
TO_CHAR | DECODE | MAX
TO_NUMBER | EXTRACT | MIN
TO_DATE | GREATEST | STDDEV
| IFNULL (See CASE and NVL) | SUM
| INTERVAL | VARIANCE
| LEAST |
| LOCATE |
| NVL |
| SUBSTR |
| USER |

## 3.2 SQL Functions Overview

SQL functions are similar to SQL operators in that both manipulate data items and both return a result. SQL functions differ from SQL operators in the format in which they appear with their arguments. The SQL function format enables functions to operate with zero, one, or more arguments.

    function(argument1, argument2, ...) alias

If passed an argument whose datatype differs from an expected datatype, most functions perform an implicit datatype conversion on the argument before execution. If passed a null value, most functions return a null value.

SQL functions are used exclusively with SQL commands within SQL statements. There are two general types of SQL functions: single row (or scalar) functions and aggregate functions. These two types differ in the number of database rows on which they act. A single row function returns a value based on a single row in a query, whereas an aggregate function returns a value based on all the rows in a query.

Single row SQL functions can appear in select lists (except in `SELECT` statements that contain a `GROUP BY` clause) and `WHERE` clauses.

Aggregate functions are the `set` functions: `AVG`, `MIN`, `MAX`, `SUM`, and `COUNT`. You must provide them with an alias that can be used by the `GROUP BY` function.

Most functions have an SQL form and an ODBC form that can differ slightly in functionality.

### 3.2.1 Number Functions

Number functions accept numeric input and return numeric values.

### 3.2.2 Character Functions

Single row character functions accept character input and can return both character and number values.

### 3.2.3 Character Functions Returning Number Values

Some character functions return only number values.

### 3.2.4 Date Functions

Date functions operate on values of the `DATE` datatype. All date functions return a value of the `DATE` datatype, except the `MONTHS_BETWEEN` function which returns a number.

### 3.2.5 Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function name follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype.

## 3.3 SQL Functions Alphabetical Listing

This section lists Oracle Database Lite SQL functions in alphabetical order and defines each function. The discussion includes:

- Syntax

- Purpose

- Argument and Description

- Examples

- Usage Notes

- ODBC Functionality (where relevant)

### 3.3.1 ADD_MONTHS

Syntax

    ADD_MONTHS(d, n)

d, a value of the Date datatype.

n, an integer that represents a number of months.

Purpose

Adds a specified date d to a specified number of months n and returns the resulting date. If the day component of argument d is the last day of the month, or if the resulting month has fewer days than the day component of d, then `ADD_MONTHS` returns the last day of the resulting month. Otherwise, `ADD_MONTHS` returns a value that has the same day component as d.

Example

    SELECT TO_CHAR(ADD_MONTHS(hiredate,1)),'DD-MM-YYYY' "Next month"FROM emp WHERE ename = 'SMITH'

Returns the following result.

    TO_CHAR(ADD_MONTHS(HIREDATE              Next month
    ---------------------------------------- ----------
    1981-01-17                               DD-MM-YYYY

### 3.3.2 ASCII

Syntax

    ASCII(char)

Purpose

Returns the decimal representation in the database character set of the first byte of char. If your database character set is 7-bit ASCII, this function returns an ASCII value.

Example

    SELECT ASCII('Q') FROM DUAL;

Returns the following result.

    ASCII('Q')
    ----------
            81

### 3.3.3 AVG

Syntax

    AVG([DISTINCT | ALL] n)

Purpose

Returns the average value of a column n.

Example 1

    SELECT AVG(SAL) FROM EMP;

Returns the following result.

    AVG(SAL)
    ---------
      2073.21

Example 2

    SELECT {FN AVG (SAL)} FROM EMP;

Returns the following result.

    {FNAVG(SAL)}
    ------------
         2073.21

Example 3

    SELECT AVG (DISTINCT DEPTNO) FROM EMP;

Returns the following result.

    AVG(DISTINCTDEPTNO)
    -------------------
                     20

Example 4

    SELECT AVG (ALL DEPTNO) FROM EMP;

Returns the following result.

    AVG(ALLDEPTNO)
    --------------
            22.142

ODBC Function

    {FN AVG ([DISTINCT | ALL] n)}

where n is the name of a numeric column.

### 3.3.4 CASE

Syntax

    CASE

    WHEN condition 1 THEN result 1
    WHEN condition 2 THEN result 2
    ...
    WHEN condition n THEN result n
    ELSE result x

    END,

Purpose

Specifies a conditional value using arguments listed in Table 3-1.

Table 3-1 Arguments Used with the CASE Function

Argument | Description
--- | ---
`WHEN` | Begins a condition clause.
`condition` | Specifies the condition.
`THEN` | Begins a result clause.
`result` | Specifies the result of the associated condition.
`ELSE` | An optional clause specifying the result of any value not described in a condition clause.
`END` | Terminates the case statement.

Usage Notes

The `CASE` function specifies conditions and results for a select or update statement. You can use the `CASE` function to search for data based on specific conditions or to update values based on a condition.

Example

    SELECT CASE JOB

    WHEN 'PRESIDENT' THEN 'The Honorable'
    WHEN 'MANAGER' THEN 'The Esteemed'
    ELSE 'The good'

    END,

    ENAME
    FROM EMP;

Returns the following result.

    CASEJOBWHEN'PRESI ENAME
    ----------------- ----------
    The Honorable     KING
    The Esteemed      BLAKE
    The Esteemed      CLARK
    The Esteemed      JONES
    The good          MARTIN
    The good          ALLEN
    The good          TURNER
    The good          JAMES
    The good          WARD
    The good          FORD
    The good          SMITH
    The good          SCOTT
    The good          ADAMS
    The good          MILLER

    14 rows selected.

### 3.3.5 CAST

Syntax

    SELECT CAST ( <source_operand > AS <data_type > ) FROM DUAL;

Purpose

Converts data from one type to another type using arguments listed in Table 3-2.

Table 3-2 Arguments Used with the CAST Function

Argument | Description
--- | ---
<source_operand> | a value expression or `NULL`.
<data_type> | the type of target.

Usage Notes

The table in Figure 3-1 displays the conversion results of source operands to datatypes.

Figure 3-1 Conversion Results of Source Operands and Datatypes

Description of "Figure 3-1 Conversion Results of Source Operands and Datatypes"

The conversion results of source operands to datatypes are defined in Table 3-3.

Table 3-3 Definitions of Conversion Results and Source Operands

Result Definitions | Source Operands
--- | ---
EN = exact number | D = date
C = fixed or variable length character | TS = timestamp
VC = variable length character | DT = date-time
T = time | V = valid
YM = year-month interval | R = valid with restrictions
AN = approximate numeric | X = invalid
FC = fixed length character |

If <source_operand> is an exact numeric and <data_type> is an interval, then the interval contains a single date-time field.

If <source_operand> is an interval and <data_type> is an exact numeric, then the interval contains a single date-time field.

If <source_operand> is a character string and <data_type> specifies a character string, then their character repertoire is the same.

If <data_type> is numeric and the result cannot be represented without losing leading significant digits, then the following exception is raised: data-exception, numeric value out of range.

Example 1

    SELECT CAST('0' AS INTEGER) FROM DUAL;

Returns the following result.

    CAST('0'ASINTEGER)
    ------------------
                     0

Example 2

    SELECT CAST(0 AS REAL) FROM DUAL;

Returns the following result.

    CAST(0ASREAL)
    -------------
                0

Example 3

    SELECT CAST(1E0 AS NUMERIC(12, 2)) FROM DUAL;

Returns the following result.

    CAST(1E0ASNUMERIC(12
    --------------------
                       1

Example 4

    SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(30)) FROM DUAL;

Returns the following result.

    CAST(CURRENT_TIMESTAMPASVARCH
    ------------------------------
    1999-04-12 14:53:53

### 3.3.6 CEIL

Syntax

    CEIL (n)

Purpose

Returns smallest integer greater than or equal to n.

Example

    SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Returns the following result.

    Ceiling
    ---------
           16

### 3.3.7 CHR

Syntax

    CHR (n)

Purpose

Returns the character with the binary equivalent to n in the database character set.

Example

    SELECT CHR(68)||CHR(79)||CHR(71) "Dog" FROM DUAL;

Returns the following result.

    Dog
    ---
    DOG

### 3.3.8 CONCAT

Syntax

    CONCAT(char1, char2)

or

    CHAR1 || CHAR2

Purpose

Returns char1 concatenated with char2, where char1 and char2 are string arguments. This function is equivalent to the concatenation operator (||).

Example

This example uses nesting to concatenate three character strings.

    SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
    FROM emp
    WHERE empno = 7900;

Returns the following result.

    Job
    -------------------------
    JAMES      is a CLERK

ODBC Function

    {FN CONCAT (char1, char2)}

### 3.3.9 CONVERT

Syntax

    { fn CONVERT(value_exp, data_type) }

Purpose

Converts a character string from one character set to another.

The value_exp argument is the value to be converted.

The data_type argument is the name of the character set to which char is converted.

Usage Notes

The common character sets are listed in Table 3-4.

Table 3-4 Common Character Sets Used with the CONVERT Function

Common Character Sets |
--- | ---
US7ASCII | WE8ISO8859P1
WE8DEC | HP West European Laserjet 8-bit character set
WE8HP | DEC French 7-bit character set
F7DEC | IBM West European EBCDIC Code Page 500
WE8EBCDIC500 | IBM PC Code Page 850 ISO 8859-1 West European 8-bit character set
WE8PC850 | ISO 8859-1 West European 8-bit character set

Example

    SELECT {fn CONVERT('Groß', 'US7ASCII') }
    "Conversion" FROM DUAL;

Returns the following result.

    conversi
    --------
    Groß

### 3.3.10 COUNT

Syntax

    COUNT([* | [DISTINCT | ALL] expr})

Purpose

Returns the number of rows in the query.

Example 1

    SELECT COUNT(*) "Total" FROM emp;

Returns the following result.

    Total
    ----------
    14

Example 2

    SELECT COUNT(job) "Count" FROM emp;

Returns the following result.

    Count
    ----------
    14

Example 3

    SELECT COUNT(DISTINCT job) "Jobs" FROM emp;

Returns the following result.

    Jobs
    ----------
    5

Example 4

    SELECT COUNT (ALL JOB) FROM EMP;

Returns the following result.

    COUNT(ALLJOB)
    -------------

### 3.3.11 CURDATE

Syntax

    { fn CURDATE ( <value_expression > ) }

Purpose

Returns the current date.

Usage Notes

If you specify expr (expression), this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

Example 1

    SELECT {fn CURDATE()} FROM DUAL;

Returns the following result.

    {FNCURDATE
    -----------
    1999-04-12

Example 2

    SELECT {fn WEEK({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNWEEK({FNCURDATE()})}
    -----------------------
                         16

### 3.3.12 CURRENT_DATE

Syntax

    CURRENT_DATE

Purpose

Returns the current date.

Example

    SELECT CURRENT_DATE FROM DUAL;

Returns the following result.

    CURRENT_DATE
    ------------
    1999-04-12

ODBC Function

    {fn CURDATE()}

### 3.3.13 CURRENT_TIME

Syntax

    CURRENT_TIME

Purpose

Returns the current time.

Example

    SELECT CURRENT_TIME FROM DUAL;

Returns the following result.

    CURRENT_T
    ---------
    15:54:18

ODBC Function

    {fn CURTIME()}

### 3.3.14 CURRENT_TIMESTAMP

Syntax

    CURRENT_TIMESTAMP

Purpose

Returns the current local date and local time as a timestamp value but only displays the current local date by default. You can view current local time information by using `CURRENT_TIMESTAMP` as a value of the `TO_CHAR` function and by including a time format. For more information, see Example 2.

Example 1

    SELECT CURRENT_TIMESTAMP FROM DUAL;

Returns the following result.

    CURRENT_TI
    ---------
    1999-04-12

Example 2

    SELECT TO_CHAR (CURRENT_TIMESTAMP, 'HH24:MM:SS, Day, Month, DD, YYYY')FROM DUAL;

Returns the following result.

    TO_CHAR(CURRENT_TIMESTAMP
    ----------------------------------------
    18:04:05, Tuesday  , April    , 06, 1999

ODBC Function

    {fn CURTIME()}

### 3.3.15 CURTIME

Syntax

    { fn CURTIME ( <value_expression > ) }

Purpose

Returns the current time.

Example 1

    SELECT {fn CURTIME()} FROM DUAL;

Returns the following result.

    {FNCURTIM
    ---------
    11:09:59

Example 2

    SELECT {fn HOUR({fn CURTIME()})} FROM DUAL;

Returns the following result.

    {FNHOUR({FNCURTIME()})}
    -----------------------
                         11

### 3.3.16 DATABASE

Syntax

    { fn DATABASE () }

Purpose

Specifies the name of the database. If you are using ODBC, the `DATABASE` function returns the name of the current default database file without the .ODB extension.

Usage Notes

A database name function returns the same value as that of `SQLGetConnectOption()` with the option `SQL_CURRENT_QUALIFIER`.

Example

The following example returns a result for users connected to the default database.

    SELECT {fn DATABASE () } FROM DUAL;

Returns the following result.

    {FNDATABASE()}
    --------------
    POLITE

### 3.3.17 DAYNAME

Syntax

    { fn DAYNAME (date_expr) }

Purpose

Returns the day of the week as a string.

Example

    SELECT {fn dayname({fn curdate()})} from dual;

Returns the current day of the week as a string.

### 3.3.18 DAYOFMONTH

Syntax

    { fn DAYOFMONTH ( <value_expression > ) }

Purpose

Returns the day of the month as an integer using arguments listed in Table 3-5.

Table 3-5 Argument Used with the DAYOFMONTH Function

Argument | Description
--- | ---
<value_expression> | Date on which the day of the month is computed. The result is between 1 and 31, where 1 represents the first day of the month.

Example 1

    SELECT {fn DAYOFMONTH ({fn CURDATE()})} FROM DUAL;

Returns the following result:

    {FNDAYOFMONTH({FNCURDATE()})
    ----------------------------
                              12

Example 2

    SELECT {fn DAYOFMONTH('1997-07-16')} "DayOfMonth" FROM DUAL;

Returns the following result.

    DayOfMonth
    ----------
            16

### 3.3.19 DAYOFWEEK

Syntax

    { fn DAYOFWEEK ( <value_expression > ) }

Purpose

Returns the day of the week as an integer using arguments listed in Table 3-6.

Table 3-6 Argument Used with the DAYOFWEEK Function

Argument | Description
--- | ---
<value_expression> | Date on which the day of the week is computed. The result is between 1 and 7, where 1 represents Sunday.

Example 1

    SELECT {fn DAYOFWEEK ({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNDAYOFWEEK({FNCURDATE()})}
    ----------------------------
                               2

Example 2

    SELECT {fn DAYOFWEEK('1997-07-16')} "DayOfWeek" FROM DUAL;

Returns the following result.

    DayOfWeek
    ------------------
    4

### 3.3.20 DAYOFYEAR

Syntax

    { fn DAYOFYEAR ( <value_expression > ) }

Purpose

Returns the day of the year as an integer using arguments listed in Table 3-7.

Table 3-7 Argument Used with the DAYOFYEAR Function

Argument | Description
--- | ---
<value_expression> | A date on which the day of the year is computed. The result is between 1 and 366.

Example 1

    SELECT {fn DAYOFYEAR ({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNDAYOFYEAR({FNCURDATE()})}
    ----------------------------
                             102

Example 2

    SELECT {fn DAYOFYEAR('1997-07-16')} "DAYOFYEAR" FROM DUAL;

Returns the following result.

    DayOfYear
    ---------
    197

### 3.3.21 DECODE

Syntax

    DECODE (expr, search, result [, search, result...] [,default])

Purpose

Search for an expression's values and then evaluate them in terms of a specified result.

Usage Notes

To evaluate an expression, Oracle Database Lite compares the expression to each search value one by one. If the expression is equal to a search, Oracle Database Lite returns the corresponding result. If no match is found, Oracle Database Lite returns default, or, if default is omitted, returns null. If the expression and search contain character data, Oracle Database Lite compares them using non-padded comparison semantics.

The search, result, and default values can be derived from expressions. Oracle Database Lite evaluates each search value only before comparing it to the expression, rather than evaluating all search values before comparing any of them with the expression. Consequently, Oracle Database Lite never evaluates a search if a previous search is equal to the expression.

Oracle Database Lite automatically converts the expression and each search value to the datatype of the first search value before making comparisons. Oracle Database Lite automatically converts the return value to the same datatype as the first result. If the first result has the datatype `CHAR` or if the first result is null, then Oracle Database Lite converts the return value to the datatype `VARCHAR2`.

In a `DECODE` expression, Oracle Database Lite considers two nulls to be equivalent. If the expression is null, Oracle Database Lite returns the result of the first search that is also null.

The maximum number of components in the `DECODE` expression, including the expression, searches, results, and default is 255.

Example 1

The following expression decodes the `DEPTNO` column in the `DEPT` table. If `DEPTNO` is 10, the expression evaluates to `'ACCOUNTING'`; if `DEPTNO` is 20, it evaluates to `'RESEARCH'`; and so on. If `DEPTNO` is not 10, 20, 30, or 40, the expression returns `'NONE'`.

    DECODE (deptno, 10, 'ACCOUNTING',
    20, 'RESEARCH',
    30, 'SALES',
    40, 'OPERATIONS',
    'NONE')

Example 2

The following example uses the `DECODE` clause in a `SELECT` statement.

    SELECT DECODE (deptno, 10, 'ACCOUNTING',
    20, 'RESEARCH',
    30, 'SALES',
    40, 'OPERATIONS',
    'NONE')
    FROM DEPT;

Returns the following result.

    DECODE(DEP
    ----------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS

### 3.3.22 EXTRACT

Syntax

    EXTRACT (extract-field FROM extract source)

Purpose

Returns information from the i portion of the extract-source. The extract-source argument contains date-time or interval expressions. The extract-field argument contains one of the following keywords: `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, or `SECOND`.

The precision of the returned value is defined in implementation. The scale is 0 unless `SECOND` is specified. When `SECOND` is specified, the scale is not less than the fractional seconds precision of the extract-source field.

Example 1

    SELECT EXTRACT (DAY FROM '06-15-1966') FROM DUAL;

Returns the following result.

    EXTRACT(DAY
    -----------
             15

Example 2

    SELECT  EXTRACT (YEAR FROM {FN CURDATE()}) FROM DUAL;

Returns the following result.

    EXTRACT(YEAR
    ------------
            1999

### 3.3.23 FLOOR

Syntax

    FLOOR (n)

Purpose

Returns largest integer equal to or less than n.

Example

    SELECT FLOOR(15.7) "Floor" FROM DUAL;

Returns the following result.

    Floor
    ---------
           15

### 3.3.24 GREATEST

Syntax

    GREATEST(expr [,expr] ...)

Purpose

Returns the greatest of the list of exprs (expressions). All exprs after the first are implicitly converted to the datatype of the first exprs before the comparison. Oracle Database Lite compares the exprs using non padded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher value. If the value returned by this function is character data, its datatype is always `VARCHAR2`.

Example

    SELECT GREATEST('HARRY','HARRIOT','HAROLD') "GREATEST" FROM DUAL;

Returns the following result.

    GREATEST
    --------
    HARRY

### 3.3.25 HOUR

Syntax

    HOUR (time_exp)

Purpose

Returns the hour as an integer value in the range of 0-23.

Example 1

    SELECT {FN HOUR ('14:03:01')} FROM DUAL;

Returns the following result.

    {FNHOUR('14:03:01')}
    --------------------
                      14

Example 2

    SELECT {fn HOUR({fn CURTIME()})} FROM DUAL;

Returns the following result.

    {FNHOUR({FNCURTIME()})}
    -----------------------
                         11

### 3.3.26 INITCAP

Syntax

    INITCAP(char)

Purpose

Returns `char`, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Example

    SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Returns the following result.

    Capitals
    --------
    The Soap

### 3.3.27 INSTR

Syntax

    INSTR(char1, char2, [, n [, m ]])

Purpose

Searches the string argument char1, beginning with its nth character, for the mth occurrence of string argument char2, where m and n are numeric arguments. Returns the position in char1 of the first character of this occurrence.

Usage Notes

If n is negative, `INSTR` counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning that `INSTR` begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1), the return value is 0. For additional information, see the syntax for the POSITION function.

Example

    SELECT INSTR('CORPORATE FLOOR','OR',3,2) "Instring" FROM DUAL;

Returns the following result.

    Instring
    ---------
           14

### 3.3.28 INSTRB

Syntax

    INSTRB(char1, char2, [, n [, m ]])

Purpose

Searches the string argument char1, beginning with its nth byte, for the mth occurrence of string argument char2, where m and n are numeric arguments. Returns the position in char1 of the first byte of this occurrence. The same as INSTR except that n and the function's return value are expressed in bytes rather than characters. For a single-byte database character set, `INSTRB` is equivalent to INSTR.

Example

    SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL;

Returns the following result.

    Instring in bytes
    -----------------
                   14

### 3.3.29 INTERVAL

Syntax

    INTERVAL (datetime values)

Purpose

Subtracts one `datetime` from another and generates the result. When you add or subtract one interval from another, the result is always another interval. You can multiply or divide an interval by a numeric constant.

Example 1

    SELECT CURRENT_DATE - INTERVAL '8' MONTH FROM DUAL;

Returns the following result.

    CURRENT_DATE-INTERVAL
    ---------------------
    1998-08-09

Example 2

    SELECT TO_CHAR (INTERVAL '6' DAY * 3) FROM DUAL;

Returns the following result.

    TO_CHAR(INTERVAL'6'DAY*3)
    -------------------------
    18

### 3.3.30 LAST_DAY

Syntax

    LAST_DAY(d)

Purpose

Returns a date that represents the last day of the month in which date d occurs.

Usage Notes

You can use this function to determine how many days are left in the current month.

Example 1

    SELECT LAST_DAY (SYSDATE) FROM DUAL;

Returns the following result.

    LAST_DAY
    ----------
    1999-04-30

Example 2

    SELECT SYSDATE,
    LAST_DAY(SYSDATE) "Last",
    LAST_DAY(SYSDATE) - SYSDATE "Days Left"
    FROM DUAL;

Returns the following result.

    {FNNOW()}   Last        Days Left
    ----------  ----------  ---------
    1999-04-12  1999-04-30         18

### 3.3.31 LEAST

Syntax

    LEAST(expr [,expr] ...)

Purpose

Returns the least of the list of exprs (expressions). All exprs after the first are implicitly converted to the datatype of the first exprs before the comparison. Oracle Database Lite compares the exprs using non-padded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is less than another if it has a lower value. If the value returned by this function is character data, its datatype is always `VARCHAR2`.

Example

    SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL;

Returns the following result.

    LEAST
    -------
    HAROLD

### 3.3.32 LENGTH

Syntax

    LENGTH (char)
    {fn LENGTH(char)}
    BIT_LENGTH (char)
    CHAR_LENGTH (char)
    OCTET_LENGTH (char)

`LENGTH` returns the number of characters in char. `BIT_LENGTH`, `CHAR_LENGTH`, and `OCTET_LENGTH` return the length of char in bits, characters, or octets, respectively.

Purpose

Returns the length in characters of the string argument char. If char has the datatype `CHAR`, the length includes all trailing blanks. If char is null, it returns null.

Usage Notes

`BIT_LENGTH`, `CHAR_LENGTH`, and `OCTET_LENGTH` are SQL-92 functions. `CHAR_LENGTH` is the same as `LENGTH`, and `OCTET_LENGTH` is the same as `LENGTHB`.

Example

    SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL;

Returns the following result.

    Length in characters
    --------------------
                       7

### 3.3.33 LENGTHB

Syntax

    LENGTHB(char)
    {fn LENGTHB(char)}

Purpose

Returns the length in bytes of the string argument char. If char is null, it returns null. For a single-byte database character set, `LENGTHB` is equivalent to LENGTH.

Example

    SELECT LENGTHB('CANDIDE') "Length in bytes" FROM DUAL;

Returns the following result.

    Length in bytes
    ---------------
                  7

### 3.3.34 LOCATE

Syntax

    LOCATE (string_exp1, string_exp2[,start])

Purpose

Returns the starting position of the first occurrence of string_exp1 within the first character position of string_exp2. You can use the start value to specify a search location other than the first character position of string_exp2.

Example 1

The following example selects the starting position of the character '`R`' in the string expression '`TURNER`' for every row of the `EMP` table.

    SELECT {FN LOCATE ('R', 'TURNER')} FROM EMP ENAME;

Returns the following result.

    {FNLOCATE('R'
    -------------
                3
                3
                3
                3
                3
                3
                3
                3
                3
                3
                3
                3
                3
                3

    14 rows selected.

Example 2

The following example selects the starting position of the character '`R`' in the string expression '`TURNER`' and starts its search at the fourth character in '`TURNER`'. The example displays the results found for every occurrence of '`TURNER`' in every row of the `EMP` table.

    SELECT {FN LOCATE ('R', 'TURNER',4)} FROM EMP ENAME;

Returns the following result.

    {FNLOCATE('R'
    -------------
                6
                6
                6
                6
                6
                6
                6
                6
                6
                6
                6
                6
                6
                6

    14 rows selected.

### 3.3.35 LOWER

Syntax

    LOWER(char)

Purpose

Returns a string argument char, with all its letters in lowercase. The return value has the same datatype as char, either `CHAR` or `VARCHAR2`.

Example

    SELECT LOWER('LOWER') FROM DUAL;

Returns the following result.

    LOWER
    -----
    lower

ODBC Function

    {fn LCASE (char)}

### 3.3.36 LPAD

Syntax

    LPAD(char1,n [,char2])

Purpose

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.

Example

    SELECT LPAD('Page1',15,'*.') "LPAD example" FROM DUAL;

Returns the following result.

    LPAD example
    -----------------
    *.*.*.*.*.Page1

### 3.3.37 LTRIM

Syntax

    LTRIM(char [, set])

Purpose

Returns the string argument char, with its left-most characters removed up to the first character which is not in the string argument set, which defaults to (a single space).

Example

    SELECT LTRIM ('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;

Returns the following result.

    LTRIM example
    ---------------
    XxyLAST WORD

ODBC Function

    {fn LTRIM (char) }      (trims leading blanks)

### 3.3.38 MAX

Syntax

    MAX([DISTINCT | ALL] expr)

Purpose

Returns the maximum value of an expression specified by the argument expr.

Example

    SELECT MAX(SAL) FROM EMP;

Returns the following result.

    MAX(SAL)
    ---------
    5000

### 3.3.39 MIN

Syntax

    MIN([DISTINCT | ALL] expr)

Purpose

Returns the minimum value of an expression specified by the argument expr.

Example

    SELECT MIN(SAL), MAX(SAL) FROM EMP;

Returns the following result.

    MIN(SAL)
    ---------
          800

### 3.3.40 MINUTE

Syntax

    MINUTE (time_exp)

Purpose

Returns the minute as an integer value in the range of 0-59.

Example 1

    SELECT {FN MINUTE ('14:03:01')} FROM DUAL;

Returns the following result.

    {FNMINUTE('14:03:01')}
    ----------------------
                         3

Example 2

    SELECT {fn MINUTE({fn CURTIME()})} FROM DUAL;

Returns the following result.

    {FNMINUTE({FNCURTIME()})}
    -------------------------
                           23

### 3.3.41 MOD

Syntax

    MOD (m,n)

Purpose

Returns the remainder of m divided by n. Returns m if n is 0.

Example

    SELECT MOD (26,11) "ABLOMOV" FROM DUAL;

Returns the following result.

    ABLOMOV
    ---------
            4

### 3.3.42 MONTH

Syntax

    MONTH (date_exp)

Purpose

Returns the month as an integer value in the range of 1-12.

Example 1

    SELECT {FN MONTH ('06-15-1966')} FROM DUAL;

Returns the following result.

    {FNMONTH('06-15-1966')}
    -----------------------
                          6

Example 2

    SELECT {fn MONTH({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNMONTH({FNCURDATE()})}
    ------------------------
                           4

### 3.3.43 MONTHNAME

Syntax

    { fn MONTHNAME (date_exp) }

Purpose

Returns the name of the month as a string.

Example

    select {fn monthname({fn curdate()})} from dual;

Returns the current month of the year as a string.

### 3.3.44 MONTHS_BETWEEN

Syntax

    MONTHS_BETWEEN(d1, d2 )

Purpose

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer. Otherwise, Oracle Database Lite calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.

Example

    SELECT MONTHS_BETWEEN(
    TO_DATE('02-02-1995','MM-DD-YYYY'),
    TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
    FROM DUAL;

Returns the following result.

    Months
    ---------
    1.0322581

### 3.3.45 NEXT_DAY

Syntax

    NEXT_DAY(d, char)

Purpose

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language. The return value has the same hours, minutes, and seconds component as the argument d.

Example

    SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY" FROM DUAL;

Returns the following result.

    NEXT DAY
    ----------
    1992-03-17

### 3.3.46 NOW

Syntax

    NOW

Purpose

Returns the current local date and local time as a timestamp value but only displays the current local date by default. You can view current local time information by using `NOW` as a value of the `TO_CHAR` function and by including a time format. For more information, see Example 2.

Example 1

    SELECT {FN NOW()} FROM DUAL;

Returns the following result.

    {FNNOW()}
    ----------
    1999-04-07

Example 2

    SELECT TO_CHAR ({fn NOW ('YYYY, Month, DD, HH24:MM:SS')}) FROM DUAL;

Returns the following result.

    TO_CHAR({FNNOW('YYYY
    ----------------------------------------
    1999-04-07 12:55:31

### 3.3.47 NVL

Syntax

    NVL(expr1, expr2)

Purpose

If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 must be of the same datatype.

Example 1

    SELECT ename, NVL(TO_CHAR(COMM),'NOT APPLICABLE') "COMMISSION"
    FROM emp
    WHERE deptno = 30;

Returns the following result.

    ENAME      COMMISSION
    ---------- ----------------
    BLAKE      NOT APPLICABLE
    MARTIN     1400.00
    ALLEN      300.00
    TURNER     .00
    JAMES      NOT APPLICABLE
    WARD       500.00

    6 rows selected.

Example 2

    SELECT {fn IFNULL(Emp.Ename, 'Unknown')},
    NVL (Emp.comm, 0) FROM EMP;

Returns the following result.

    {FNIFNULL( 'UNKNOWN')}
    ---------- -----------
    KING                 0
    BLAKE                0
    CLARK                0
    JONES                0
    MARTIN            1400
    ALLEN              300
    TURNER               0
    JAMES                0
    WARD               500
    FORD                 0
    SMITH                0
    SCOTT                0
    ADAMS                0
    MILLER               0

    14 rows selected.

Example 3

    SELECT sal+NVL(comm, 0) FROM EMP;

Returns the following result.

    SAL+NVL(COMM
    ------------
            5000
            2850
            2450
            2975
            2650
            1900
            1500
             950
            1750
            3000
             800
            3000
            1100
            1300

    14 rows selected.

ODBC Function

    {fn IFNULL (expr1, expr2)}

### 3.3.48 POSITION

Syntax

    POSITION ( <substring_value_expression>
                    IN <value_expression> )

The arguments for the `POSITION` function are listed in Table 3-8.

Table 3-8 Arguments Used with the POSITION Function

Argument | Description
--- | ---
<value_expression> | a source string to search in.
<substring_value_expression> | a sub-string to search for.
<start_len_cnt> | the starting position for the search

Purpose

Returns the starting position of the first occurrence of a sub-string in a string.

Usage Notes

If the length of <substring_value_expression> is 0, the result is null. If <substring_value_expression> occurs in <value_expression>, the result is the position of the first character of <substring_value_expression>. Otherwise, the result is 0. If <start_len_cnt> is omitted, the function starts the search from position 1. For additional information, see the INSTR and INSTRB functions.

Example

    SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;

Returns the following result.

    POSITION('CAT'IN'CATCH')
    ------------------------
                           1

ODBC Function

    {fn LOCATE ( <substring_value_expression> ,
      <value_expression>[, <start_len_cnt> ] ) }

### 3.3.49 QUARTER

Syntax

    { fn QUARTER ( <value_expression> ) }

The arguments for the `QUARTER` function are listed in Table 3-9.

Table 3-9 Arguments Used with the QUARTER Function

Argument | Description
--- | ---
<value_expression> | A date on which the quarter is computed. The result is between 1 and 4, where 1 represents January 1 through March 31.

Purpose

Returns the quarter of a date as an integer.

Example

    SELECT {fn QUARTER ({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNQUARTER({FNCURDATE()})}
    --------------------------
                             2

### 3.3.50 REPLACE

Syntax

    REPLACE(char, search_string [, replacement_string])

Purpose

Returns char with every occurrence of search_string replaced with replacement_string, where char, search_string, and replacement_string are string arguments.

Usage Notes

If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, then char is returned. This function provides a super-set of the functionality provided by the TRANSLATE function. `TRANSLATE` provides single character, one to one, and substitution functions. `REPLACE` enables you to substitute one string for another as well as to remove character strings.

Example

    SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Returns the following result.

    Changes
    ---------------
    BLACK and BLUE

### 3.3.51 ROUND - Date Function

Syntax

    ROUND(d [,fmt])

The format models to be used with the `ROUND` (and `TRUNC`) date function, and the units to which it rounds dates are listed in Table 3-10. The default model, `DD`, returns the date rounded to the day with a time of midnight.

Table 3-10 The Format Models with the ROUND Date Function

Formal Model | Rounding Unit
--- | ---
`CC` or `SCC` | Century
`YYYY`, `SYYYY`, `YEAR`, `SYEAR`, `YYY`, `YY`, `Y` | Year (rounds up on July 1)
`IYYY`, `IYY`, `IY`, `I` | ISO Year
`Q` | Quarter (rounds up in the sixteenth day of the second month of the quarter)
`MONTH`, `MON`, `MM`, `RM` | Month (rounds up on the sixteenth day)
`WW` | Same day of the week as the first day of the year
`IW` | Same day of the week as the first day of the ISO year
`W` | Same day of the week as the first day of the month
`DDD`, `DD`, `J` | Day
`DAY`, `DY`, `D` | Starting day of the week.
`HH`, `HH12`, `HH24` | Hour
`MI` | Minute

Purpose

Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day.

Example

    SELECT ROUND(TO_DATE('27-OCT-92'),'YEAR')
    "FIRST OF THE YEAR" FROM DUAL;

Returns the following result.

    FIRST OF
    ---------
    1993-01-0

### 3.3.52 ROUND - Number Function

Syntax

    ROUND(n [,m ])

Purpose

Returns n rounded to m places to the right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

Example 1

    SELECT ROUND (54.339, 2) FROM DUAL;

Returns the following result.

    ROUND(54.339
    ------------
    54.34

### 3.3.53 RPAD

Syntax

    RPAD(char1,n [,char2 ])

Purpose

Returns char1, right-padded to length n with char2 replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.

Example

    SELECT RPAD('ename',12,'ab') "RPAD example"
    FROM emp
    WHERE ename = 'TURNER';

Returns the following result.

    RPAD example
    -------------
    enameabababa

### 3.3.54 RTRIM

Syntax

    RTRIM(char [,set])

Purpose

Returns the string argument char, with its right-most characters removed following the last character which is not in the string argument set. This defaults to ' ' (a single space).

Example 1

    SELECT RTRIM ('TURNERyxXxy', 'xy') "RTRIM example" FROM DUAL;

Returns the following result.

    RTRIM examp
    -----------
    TURNERyxX

Example 2

    SELECT {fn RTRIM ('TURNERyxXxy', 'xy')} FROM DUAL;

Returns the following result.

    {{FNRTRIM('T
    -----------
    TURNERyxX

ODBC Function

    {fn RTRIM (char)}    (trims leading blanks)

### 3.3.55 SECOND

Syntax

    SECOND (time_exp)

Purpose

Returns the second as an integer value in the range of 0-59.

Example 1

    SELECT {FN SECOND ('14:03:01')} FROM DUAL;

Returns the following result.

    {FNSECOND('14:03:01')}
    ----------------------
                         1

Example 2

    SELECT {fn SECOND({fn CURTIME()})} FROM DUAL;

Returns the following result.

    {FNSECOND({FNCURTIME()})}
    -------------------------
                           59

### 3.3.56 STDDEV

Syntax

    STDDEV([DISTINCT|ALL] x)

Purpose

Returns the standard deviation of x, a number. Oracle Database Lite calculates the standard deviation as the square root of the variance defined for the VARIANCE group function.

Example

    SELECT STDDEV(sal) "Deviation" FROM emp;

Returns the following result.

    Deviation
    ---------
    1182.5032

### 3.3.57 SUBSTR

Syntax

    SUBSTR(char, m [, n ])

Purpose

Returns a portion of the string argument char, beginning with the character at position m and n characters long.

Usage Notes

If m is positive, `SUBSTR` counts from the beginning of char to find the first character. If m is negative, `SUBSTR` counts backwards from the end of char. The value m cannot be 0. If n is omitted, `SUBSTR` returns all characters to the end of char. The value n cannot be less than 1.

Example

    SELECT SUBSTR('ABCDEFG',3,4) "Subs" FROM DUAL;

Returns the following result.

    Subs
    ----
    CDEF

### 3.3.58 SUBSTRB

Syntax

    SUBSTRB(char, m [,n])

Purpose

Returns a portion of the string argument char, beginning with the byte at position m and n bytes long. The same as SUBSTR, except that the arguments m and n specify bytes rather than characters. For a single-byte database character set, `SUBSTRB` is equivalent to SUBSTR.

Example

    SELECT SUBSTRB('ABCDEFG',5,4) "Substring with bytes" FROM DUAL;

Returns the following result.

    Substring with bytes
    --------------------
    EFG

### 3.3.59 SUM

Syntax

    SUM([DISTINCT | ALL] n)

Purpose

Returns the sum of values of n.

Example

    SELECT deptno, SUM(sal) TotalSalary FROM emp GROUP BY deptno;

Returns the following result.

    DEPTNO TOTALSALARY
    --------- -----------
           10        8750
           20       10875
           30        9400

### 3.3.60 SYSDATE

Syntax

    SYSDATE

Purpose

Returns the current date and time. Requires no arguments.

Usage Notes

You cannot use this function in the condition of the Oracle Database Lite `DATA` type column. You can only use the time in a `TIME` column, and both date and time in a `TIMESTAMP` column.

Example

    SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') NOW FROM DUAL;

Returns the following result.

    NOW
    -------------------
    04-12-1999 19:13:48

### 3.3.61 TIMESTAMPADD

Syntax

    {fn TIMESTAMPADD (<interval>, <value_exp1 >, <value_exp2 >)}
    <value_exp1 > + <value_exp2 >

The arguments for the `TIMESTAMPADD` function are listed in Table 3-11.

Table 3-11 Arguments Used with the TIMESTAMPADD Function

Argument | Description
--- | ---
<interval> | Specifies the unit of the second operand, <value_exp1>. The following keywords are valid values for intervals. `SQL_TSI_FRAC_SECOND` `SQL_TSI_SECOND` `SQL_TSI_MINUTE` `SQL_TSI_HOUR` `SQL_TSI_DAY` `SQL_TSI_WEEK` `SQL_TSI_MONTH` `SQL_TSI_QUARTER` `SQL_TSI_YEAR`
<value_exp1> | an integer
<value_exp2> | a timestamp
<value_expression> | an operand

Purpose

Adds a date and time value to the current timestamp.

Example

The following example adds one day to the current timestamp for 1999-04-13.

    SELECT {fn TIMESTAMPADD (SQL_TSI_DAY, 1, {fn NOW()})} FROM DUAL;

Returns the following result.

    {FNTIMESTA
    ----------
    1999-04-14

### 3.3.62 TIMESTAMPDIFF

Syntax

    {fn TIMESTAMPDIFF (<interval>, <value_exp1 >, <value_exp2 >)}
    <value_expression > - <value_expression >

The arguments for the `TIMESTAMPDIFF` function are listed in Table 3-12.

Table 3-12 Arguments Used with the TIMESTAMPDIFF Function

Argument | Description
--- | ---
<interval> | specifies the unit of the second operand, <value_exp1>. The following keywords are valid values for intervals: `SQL_TSI_FRAC_SECOND` `SQL_TSI_SECOND` `SQL_TSI_MINUTE` `SQL_TSI_HOUR` `SQL_TSI_DAY` `SQL_TSI_WEEK` `SQL_TSI_MONTH` `SQL_TSI_QUARTER` `SQL_TSI_YEAR`
<value_exp1> | an integer
<value_exp2> | a timestamp
<value_expression> | an operand

Purpose

Calculates the difference between two timestamp values using a specified interval.

Example 1

    SELECT {fn TIMESTAMPDIFF (SQL_TSI_DAY, {fn CURDATE()}, '1998-12-09')} FROM DUAL;

Returns the following result.

    {FNTIMESTAMPDIFF(SQL_TSI_DAY
    ----------------------------
                            -125

Example 2

    SELECT ENAME, {fn TIMESTAMPDIFF (SQL_TSI_YEAR, {fn CURDATE()},HIREDATE)} FROM EMP;

Returns the following result.

    ENAME      {FNTIMESTAMPDIFF(SQL_TSI_YEA
    ---------- ----------------------------
    KING                                -17
    BLAKE                               -17
    CLARK                               -17
    JONES                               -18
    MARTIN                              -17
    ALLEN                               -18
    TURNER                              -17
    JAMES                               -17
    WARD                                -18
    FORD                                -17
    SMITH                               -18
    SCOTT                               -16
    ADAMS                               -16
    MILLER                              -17

    14 rows selected.

### 3.3.63 TO_CHAR

Syntax for Dates

    TO_CHAR(d [, fmt])

Syntax for Numbers

    TO_CHAR(n [, fmt])

Purpose

Converts a date or number to a value of the `VARCHAR2` datatype, using the optional format fmt using arguments listed in Table 3-13.

Table 3-13 Arguments Used with the TO_CHAR Function

Argument | Description
--- | ---
d | date column or `SYSDATE`
fmt | format model. The format model is fully described in the Oracle Database SQL Reference.
n | number column or literal

Usage Notes

- If you omit fmt, the argument d or n is converted to a `VARCHAR2` value. For dates, the argument d is returned in the default date format. For numbers, the argument n is converted to a value exactly long enough to hold its significant digits.

- Date literals must be preceded by the `DATE` keyword when used as arguments to `TO_CHAR`.

You can specify a default date format for all databases on your computer by setting the `NLS_DATE_FORMAT` parameter in the POLITE.INI file. See Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information on setting the `NLS_DATE_FORMAT` parameter in the POLITE.INI file.

Example

    SELECT TO_CHAR (SYSDATE, 'Day, Month, DD, YYYY')"TO_CHAR example" FROM DUAL;

Returns the following result.

    TO_CHAR example
    --------------------------------
    Saturday , May      , 22, 1999

### 3.3.64 TO_DATE

Syntax

    TO_DATE(char [, fmt ])

Purpose

Converts the character string argument char to a value of the DATE datatype. The fmt argument is a date format specifying the format of char.

Example

    SELECT TO_DATE('January 26, 1996, 12:38 A.M.', 'Month dd YYYY HH:MI A.M.') FROM DUAL;

Returns the following result.

    TO_CHAR(TO_DATE('JANUARY26
    ----------------------------------------
    1996-01-26 12:38:00

### 3.3.65 TO_NUMBER

Syntax

    TO_NUMBER(char [, fmt ])

Purpose

Converts the string argument char that contains a number in the format specified by the optional format model fmt, to a return value of the `NUMBER` datatype.

Usage Notes

- For information about date and number formats, see Formats.

- Do not use the `TO_DATE` function with a `DATE` value for the char argument.

- The returned `DATE` value can have a different century value than the original char, depending on fmt or the default date format.

- Dates in the Oracle format (such as `06-JUN-85` and `6-JUN-1985`), the SQL-92 format (such as `1989-02-28`), or the format specified by the `NLS_DATE_FORMAT` parameter are converted automatically when inserted into a date column.

- You can specify a default date format for all databases on your computer by setting the `NLS_DATE_FORMAT` parameter in the POLITE.INI file. See the Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information on setting the `NLS_DATE_FORMAT` parameter in the POLITE.INI file.

Example

The following example updates the salary of an employee named Blake according to the value specified in the `TO_NUMBER` function. In this example, you first view Blake's salary. Then, update Blake's salary and view it again.

    SELECT * FROM EMP WHERE ENAME = 'BLAKE';

Returns the following result.

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
    --------- ---------- --------- --------- --------- --------- --------- ---------
         7698 BLAKE      MANAGER        7839 1981-05-0      2850                  30

    UPDATE EMP SET SAL = SAL + TO_NUMBER('100.52','9,999.99') WHERE ENAME = 'BLAKE';

Returns the following result.

    1 row updated.

    SELECT * FROM EMP WHERE ENAME = 'BLAKE';

Returns the following result.

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
    --------- ---------- --------- --------- --------- --------- --------- ---------
         7698 BLAKE      MANAGER        7839 1981-05-0   2950.52                  30

### 3.3.66 TRANSLATE

Syntax

    TRANSLATE(char, from, to)

Purpose

Returns char with all occurrences of each character in from replaced by its corresponding character in to, where char, from, and to are string arguments.

Usage Notes

- Characters in char that are not in from are not replaced.

- The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value.

You cannot use an empty string for to to remove from the return value all characters in from. `TRANSLATE` interprets the empty string as null, and if this function has a null argument, it returns null.

Example

    SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
    '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;

Returns the following result.

    Licence
    -------
    9XXX999

### 3.3.67 TRIM

Syntax

    TRIM( [[<trim_spec >] char ]
        FROM ] string )

If <trim_spec> is omitted, then BOTH is implied. If char is omitted, then a space character is implied as listed in Table 3-14.

Table 3-14 Arguments Used with the TRIM Function

Argument | Description
--- | ---
<trim_spec> | a specification: `LEADING`, `TRAILING`, or `BOTH`
char | a single character
string | the target string to be trimmed

Purpose

Removes leading and/or trailing blanks (or other characters) from a string.

Example

    SELECT TRIM ('OLD' FROM 'OLDMAN') FROM DUAL;

Returns the following result.

    TRIM('
    ------
    MAN

### 3.3.68 TRUNC

Syntax with Numeric Arguments

    TRUNC(n [, m])

Syntax with Date Arguments

    TRUNC(d [, fmt])

Purpose with Numeric Arguments

Returns n truncated to m decimal places, where m and n are numeric arguments. If m is omitted, truncates to 0 places. If m is negative, truncates (makes zero) m digits to the left of the decimal point.

Purpose with Date Arguments

Returns the date d with its time portion truncated to the time unit specified by the format model fmt. If you omit fmt, then d is truncated to the nearest day.

Usage Notes

The format models to be used with the `TRUNC` (and `ROUND`) date function, and the units to which it rounds dates are listed in Table 3-15. The default model, `DD`, returns the date rounded to the day with a time of midnight.

Table 3-15 Arguments Used with the TRUNC Function

Format Model | Rounding Unit
--- | ---
`CC` or `SCC` | Century
`YYYY`, `SYYYY`, `YEAR`, `SYEAR`, `YYY`, `YY`, `Y` | Year (rounds up on July 1)
`IYYY`, `IYY`, `IY`, `I` | ISO Year
`Q` | Quarter (rounds up in the sixteenth day of the second month of the quarter)
`MONTH`, `MON`, `MM`, `RM` | Month (rounds up on the sixteenth day)
`WW` | Same day of the week as the first day of the year
`IW` | Same day of the week as the first day of the ISO year
`W` | Same day of the week as the first day of the month
`DDD`, `DD`, `J` | Day
`DAY`, `DY`, `D` | Starting day of the week
`HH`, `HH12`, `HH24` | Hour
`MI` | Minute

Example 1

    SELECT TRUNC(TO_DATE('27-OCT-92', 'DD-MON-YY'), 'YEAR') "First Of The Year"
    FROM DUAL;

Returns the following result.

    First Of T
    ----------
    1992-01-01

Example 2

    SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

Returns the following result.

    Truncate
    ---------
         15.7

Example 3

    SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

Returns the following result.

    Truncate
    ---------
           10

### 3.3.69 UPPER

Syntax

    UPPER(char)

Purpose

Returns the string argument char with all its letters converted to uppercase. The return value has the same datatype as char.

Example

    SELECT UPPER('Carol') FROM DUAL;

Returns the following result.

    UPPER
    -----
    CAROL

ODBC Function

    {fn UCASE (char)}

### 3.3.70 USER

Syntax

    USER

Purpose

Returns the current schema name as a character string.

Example 1

    SELECT USER "User" FROM DUAL;

Returns the following result.

    User
    --------
    SYSTEM

Example 2

    SELECT {fn USER()} FROM DUAL;

Returns the following result.

    {FNUSER()}
    ------------------------------
    SYSTEM

ODBC Function

    { fn USER()}

### 3.3.71 VARIANCE

Syntax

    VARIANCE([DISTINCT|ALL] x)

Purpose

Returns variance of x, a number. Oracle Lite calculates the variance of x using this formula.

xi is one of the elements of x.

n is the number of elements in the set x. If n is 1, the variance is defined to be 0.

Example

    SELECT VARIANCE(sal) "Variance" FROM emp;

Returns the following result.

    Variance
    ---------
    1398313.9

### 3.3.72 WEEK

Syntax

    { fn WEEK ( <value_expression> ) }

Purpose

Returns the week of the year as an integer using arguments listed in Table 3-16.

Table 3-16 Arguments Used with the WEEK Function

Argument | Description
--- | ---
<value_expression> | A date on which the week is computed. The result is between 1 and 53.

Example 1

    SELECT {fn WEEK({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNWEEK({FNCURDATE()})}
    -----------------------
                         16

Example2

    SELECT {fn week('1999-06-15')} FROM DUAL;

Returns the following.

    EK('1999-06-15')}
    -----------------
                   25

### 3.3.73 YEAR

Syntax

    YEAR (date_exp)

Purpose

Returns the `YEAR` as an integer.

Example 1

    SELECT {FN YEAR ('06-15-1966')} FROM DUAL;

Returns the following result.

    {FNYEAR('06-15-1966')}
    ----------------------
                      1966

Example 2

    SELECT {fn YEAR({fn CURDATE()})} FROM DUAL;

Returns the following result.

    {FNYEAR({FNCURDATE()})}
    -----------------------
                       1999