3
Operators, Functions, Expressions, Conditions

This chapter describes methods of manipulating individual data items. Standard arithmetic operators such as addition and subtraction are discussed, as well as less common functions such as absolute value and string length. Topics include:

• Operators
• SQL Functions
• User Functions
• Format Models
• Expressions
• Conditions 

 

Note:  Functions, expressions, and descriptions preceded by  are available only if the Oracle objects option is installed on your database server.

   

Operators

An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL. Tables in this section list SQL operators.

Unary and Binary Operators

There are two general classes of operators:

unary	A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:
	operator operand
binary	A binary operator operates on two operands. A binary operator appears with its operands in this format:
	operand1 operator operand2

 

Other operators with special formats accept more than two operands. If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||).

Precedence

Precedence is the order in which Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. Oracle evaluates operators with equal precedence from left to right within an expression.

Table 3-1 lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.

Table 3-1 SQL Operator Precedence

Example

In the following expression multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.

1+2*3 

You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.

SQL also supports set operators (UNION, UNION ALL, INTERSECT, and MINUS), which combine sets of rows returned by queries, rather than individual data items. All set operators have equal precedence.

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Table 3-2 lists arithmetic operators.

Table 3-2 Arithmetic Operators

SELECT * FROM orders
 WHERE qtysold = -1;
SELECT * FROM emp
  WHERE -sal < 0;

UPDATE emp
  SET sal = sal * 1.1;

SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

Do not use two consecutive minus signs with no separation (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. You should separate consecutive minus signs with a space or a parenthesis. For more information on comments within SQL statements, see "Comments".

Concatenation Operator

The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.

Table 3-3 Concatenation Operator

SELECT 'Name is ' || ename
   FROM emp;

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes. For more information on the differences between the CHAR and VARCHAR2 datatypes, see "Character Datatypes".

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.

Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

Example

This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values, concatenating them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.

CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),
        col3 VARCHAR2(6), col4 CHAR(6) );

Table created.

INSERT INTO tab1 (col1,  col2,     col3,     col4)
        VALUES   ('abc', 'def   ', 'ghi   ', 'jkl');

1 row created.

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Concatenation
------------------------
abcdef   ghi   jkl

Comparison Operators

Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. For information on conditions, see "Conditions". Table 3-4 lists comparison operators.

Table 3-4 Comparison Operators

=

SELECT *
  FROM emp
  WHERE sal = 1500;

!=
^=
<>
¬= 

SELECT *
  FROM emp
  WHERE sal != 1500;

>

<

SELECT * FROM emp
  WHERE sal > 1500;
SELECT * FROM emp
  WHERE sal < 1500;

>=

<=

SELECT * FROM emp
  WHERE sal >= 1500;
SELECT * FROM emp
  WHERE sal <= 1500;

IN

SELECT * FROM emp
  WHERE job IN
  ('CLERK','ANALYST');
SELECT * FROM emp
  WHERE sal IN
  (SELECT sal FROM emp
   WHERE deptno = 30);

NOT IN

SELECT * FROM emp
  WHERE sal NOT IN
  (SELECT sal FROM emp
  WHERE deptno = 30);
SELECT * FROM emp
  WHERE job NOT IN
  ('CLERK', ANALYST');

ANY
SOME

SELECT * FROM emp
  WHERE sal = ANY
  (SELECT sal FROM emp
  WHERE deptno = 30);

ALL

SELECT * FROM emp
  WHERE sal >=
  ALL ( 1400, 3000);

[NOT] 
BETWEEN 
x AND y

SELECT * FROM emp
  WHERE sal
  BETWEEN 2000 AND 3000;

EXISTS

SELECT ename, deptno 
  FROM dept
  WHERE EXISTS
  (SELECT * FROM emp
    WHERE dept.deptno 
    = emp.deptno);

x [NOT] 
LIKE y 

[ESCAPE 
'z']

SELECT * FROM tab1
  WHERE col1 LIKE
  'A_C/%E%' ESCAPE '/';

IS 
[NOT] 
NULL

SELECT ename, deptno
  FROM emp
  WHERE comm IS NULL;

Additional information on the NOT IN and LIKE operators appears in the sections that follow.

NOT IN Operator

If any item in the list following a NOT IN operation is null, all rows evaluate to UNKNOWN (and no rows are returned). For example, the following statement returns the string 'TRUE' for each row:

SELECT 'TRUE' 
    FROM emp 
    WHERE deptno NOT IN (5,15); 

However, the following statement returns no rows:

SELECT 'TRUE' 
    FROM emp 
    WHERE deptno NOT IN (5,15,null); 

The above example returns no rows because the WHERE clause condition evaluates to:

deptno != 5 AND deptno != 15 AND deptno != null 

Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

LIKE Operator

The LIKE operator is used in character string comparisons with pattern matching. The syntax for a condition using the LIKE operator is shown in this diagram:
 

where:

char1	is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.
NOT	logically inverts the result of the condition, returning FALSE if the condition evaluates to TRUE and TRUE if it evaluates to FALSE.
char2	is the pattern to which char1 is compared. The pattern is a value of datatype CHAR or VARCHAR2 and can contain the special pattern matching characters % and _.
ESCAPE	identifies a single character as the escape character. The escape character can be used to cause Oracle to interpret % or _ literally, rather than as a special character, in the pattern.   If you wish to search for strings containing an escape character, you must specify this character twice. For example, if the escape character is '/', to search for the string 'client/server', you must specify, 'client//server'.

 

While the equal (=) operator exactly matches one character value to another, the LIKE operator matches a portion of one character value to another by searching the first value for the pattern specified by the second. Note that blank padding is not used for LIKE comparisons.

With the LIKE operator, you can compare a value to a pattern rather than to a constant. The pattern can only appear after the LIKE keyword. For example, you can issue the following query to find the salaries of all employees with names beginning with 'SM':

SELECT sal 
    FROM emp 
    WHERE ename LIKE 'SM%';

The following query uses the = operator, rather than the LIKE operator, to find the salaries of all employees with the name 'SM%':

SELECT sal 
    FROM emp 
    WHERE ename = 'SM%';

The following query finds the salaries of all employees with the name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than as a pattern, because it precedes the LIKE operator:

SELECT sal 
    FROM emp 
    WHERE 'SM%' LIKE ename;

Patterns usually use special characters that Oracle matches with different characters in the value:

• An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
• A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. Note that the pattern '%' cannot match a null.

Case Sensitivity and Pattern Matching

Case is significant in all conditions comparing character expressions including the LIKE and equality (=) operators. You can use the UPPER() function to perform a case-insensitive match, as in this condition:

UPPER(ename) LIKE 'SM%'

Pattern Matching on Indexed Columns

When LIKE is used to search an indexed column for a pattern, Oracle can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle can scan the index by this leading character. If the first character in the pattern is "%" or "_", the index cannot improve the query's performance because Oracle cannot scan the index.

Example 1

This condition is true for all ENAME values beginning with "MA":

ename LIKE 'MA%' 

All of these ENAME values make the condition TRUE:

MARTIN, MA, MARK, MARY 

Case is significant, so ENAME values beginning with "Ma," "ma," and "mA" make the condition FALSE.

Example 2

Consider this condition:

ename LIKE 'SMITH_' 

This condition is true for these ENAME values:

SMITHE, SMITHY, SMITHS 

This condition is false for 'SMITH', since the special character "_" must match exactly one character of the ENAME value.

ESCAPE Option

You can include the actual characters "%" or "_" in the pattern by using the ESCAPE option. The ESCAPE option identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.

Example:

To search for any employees with the pattern 'A_B' in their name:

SELECT ename 
    FROM emp 
    WHERE ename LIKE '%A\_B%' ESCAPE '\';

The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Patterns Without %

If a pattern does not contain the "%" character, the condition can be TRUE only if both operands have the same length.

Example:

Consider the definition of this table and the values inserted into it:

CREATE TABLE freds (f CHAR(6), v VARCHAR2(6));
INSERT INTO freds VALUES ('FRED', 'FRED');

Because Oracle blank-pads CHAR values, the value of F is blank-padded to 6 bytes. V is not blank-padded and has length 4.

Logical Operators

A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 3-5 lists logical operators.

Table 3-5 Logical Operators

SELECT *
  FROM emp
  WHERE NOT (job IS NULL);
SELECT *
  FROM emp
  WHERE NOT 
  (sal BETWEEN 1000 AND 2000);

SELECT *
  FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;

SELECT *
  FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:

SELECT * 
    FROM emp 
    WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') 
        AND sal > 1000;

NOT Operator

Table 3-6 shows the result of applying the NOT operator to a condition.

Table 3-6 NOT Truth Table

AND Operator

Table 3-7 shows the results of combining two expressions with AND.

Table 3-7 AND Truth Table

OR Operator

Table 3-8 shows the results of combining two expressions with OR.

Table 3-8 OR Truth Table

Set Operators

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-9 lists SQL set operators.

Table 3-9 Set Operators

All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order. To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR, the returned values have datatype CHAR.
• If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2.

Examples

Consider these two queries and their results:

SELECT part
    FROM orders_list1;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 

SELECT part 
    FROM orders_list2;

PART 
---------- 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

The following examples combine the two query results with each of the set operators.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows how datatype must match when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_null(null), date_in
    FROM orders_list2;

PART       PARTNUM DATE_IN
---------- ------- -------- 
SPARKPLUG  3323165 
SPARKPLUG          10/24/98
FUEL PUMP  3323162
FUEL PUMP          12/24/99
TAILPIPE   1332999
TAILPIPE           01/01/01
CRANKSHAFT 9394991
CRANKSHAFT         09/12/02

SELECT part 
    FROM orders_list1 
UNION 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 

UNION ALL Example

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

Note that the UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. A PART value that appears multiple times in either or both queries (such as 'FUEL PUMP') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.

INTERSECT Example

The following statement combines the results with the INTERSECT operator which returns only those rows returned by both queries:

SELECT part 
    FROM orders_list1 
INTERSECT 
SELECT part 
    FROM orders_list2;

PART 
---------- 
TAILPIPE 

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part 
    FROM orders_list1 
MINUS 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP

Other Operators

Table 3-10 lists other SQL operators.

Table 3-10 Other SQL Operators

SELECT ename, dname
  FROM emp, dept
  WHERE dept.deptno =
     emp.deptno(+);

SELECT empno, ename, mgr
  FROM emp
  CONNECT BY
     PRIOR empno = mgr;

SQL Functions

A SQL function is similar to an operator in that it manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...) 

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion".

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.

SQL functions should not be confused with user functions written in PL/SQL. User functions are described in "User Functions".

In the syntax diagrams for SQL functions, arguments are indicated with their datatypes following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference.

SQL functions are of these general types:

• single-row (or scalar) functions
• group (or aggregate) functions

The two types of SQL functions differ in the number of rows upon which they act. A single-row function returns a single result row for every row of a queried table or view; a group function returns a single result row for a group of queried rows.

Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.

Group functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing group functions, or constants. Oracle applies the group functions in the select list to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, Oracle applies group functions in the select list to all the rows in the queried table or view. You use group functions in the HAVING clause to eliminate groups from the output based on the results of the group functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY and HAVING clauses, see the GROUP BY Clause and the HAVING Clause.

In the sections that follow, functions are grouped by the datatypes of their arguments and return values.

Number Functions

Number functions accept numeric input and return numeric values. This section lists the SQL number functions. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.

ABS

SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
        15

ACOS

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367

ASIN

SELECT ASIN(.3) "Arc_Sine" FROM DUAL;

 Arc_Sine
----------
.304692654

ATAN

SELECT ATAN(.3) "Arc_Tangent" FROM DUAL;

Arc_Tangent
----------
.291456794

ATAN2

SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL;
 
Arc_Tangent2
------------
  .982793723

CEIL

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

   Ceiling
----------
        16

COS

SELECT COS(180 * 3.14159265359/180)
"Cosine of 180 degrees" FROM DUAL;

Cosine of 180 degrees
---------------------
                   -1

COSH

SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL;
 
Hyperbolic cosine of 0
----------------------
                     1 

EXP

SELECT EXP(4) "e to the 4th power" FROM DUAL;

e to the 4th power
------------------
          54.59815 

FLOOR

SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor
----------
        15

LN

SELECT LN(95) "Natural log of 95" FROM DUAL;

Natural log of 95
-----------------
       4.55387689  

LOG

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;

Log base 10 of 100
------------------
                 2 

MOD

MOD(m,n)

SELECT MOD(11,4) "Modulus" FROM DUAL;

   Modulus
----------
         3

POWER

SELECT POWER(3,2) "Raised" FROM DUAL;

    Raised
----------
         9

ROUND

ROUND(n[,m])

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20 

SIGN

SIGN(n)

SELECT SIGN(-15) "Sign" FROM DUAL;

      Sign
----------
        -1

SIN

SELECT SIN(30 * 3.14159265359/180)
 "Sine of 30 degrees" FROM DUAL;

Sine of 30 degrees
------------------
                .5

SINH

SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL;

Hyperbolic sine of 1
--------------------
          1.17520119

SQRT

SELECT SQRT(26) "Square root" FROM DUAL;

Square root
-----------

TAN

SELECT TAN(135 * 3.14159265359/180)
"Tangent of 135 degrees"  FROM DUAL;

Tangent of 135 degrees
----------------------
                   - 1

TANH

SELECT TANH(.5) "Hyperbolic tangent of .5" 
   FROM DUAL;

Hyperbolic tangent of .5
------------------------
              .462117157 

TRUNC

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

  Truncate
----------
      15.7

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

  Truncate
----------
        10

Character Functions

Single-row character functions accept character input and can return either character or number values.

Character Functions Returning Character Values

This section lists character functions that return character values. Unless otherwise noted, these functions all return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message.

CHR

CHR(n [USING NCHAR_CS])

SELECT CHR(67)||CHR(65)||CHR(84) "Dog"
  FROM DUAL;
Dog
---
CAT

SELECT CHR(16705 USING NCHAR_CS) FROM DUAL;
 
C
-
A

CONCAT

CONCAT(char1, char2)

INITCAP

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

Capitals
---------
The Soap

LOWER

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
   FROM DUAL;

Lowercase
--------------------
mr. scott mcmillan 

LPAD

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

LPAD example
---------------
*.*.*.*.*Page 1

LTRIM

LTRIM(char [,set])

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

LTRIM exampl
------------
XxyLAST WORD

NLS_INITCAP

SELECT NLS_INITCAP
   ('ijsland', 'NLS_SORT = XDutch') "Capitalized"
   FROM DUAL;

Capital
-------
IJsland

NLS_LOWER

NLS_LOWER(char [, 'nlsparams'] )

SELECT NLS_LOWER
   ('CITTA''', 'NLS_SORT = XGerman') "Lowercase"
   FROM DUAL;

Lower
-----
cittá

NLS_UPPER

NLS_UPPER(char [, 'nlsparams'] )

SELECT NLS_UPPER
   ('groe', 'NLS_SORT = XGerman') "Uppercase"
     FROM DUAL;

Upper
-----
GROSS 

REPLACE

REPLACE(char,search_string[,replacement_string])

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

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

RPAD

RPAD(char1, n [,char2])

SELECT RPAD('MORRISON',12,'ab') "RPAD example"
     FROM DUAL;

RPAD example
-----------------
MORRISONabab

RTRIM

RTRIM(char [,set]

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
     FROM DUAL;
 
RTRIM e.g
-------------
BROWNINGyxX

SOUNDEX

SOUNDEX(char)

SELECT ename
     FROM emp
     WHERE SOUNDEX(ename)
         = SOUNDEX('SMYTHE');
 
ENAME
----------
SMITH

SUBSTR

SUBSTR(char, m [,n])

SELECT SUBSTR('ABCDEFG',3.1,4) "Subs"
     FROM DUAL;
 
Subs
----
CDEF

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

Subs
----
CDEF

SUBSTRB

SUBSTR(char, m [,n])

TRANSLATE

TRANSLATE(char, from, to)

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999 

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'0123456789')
"Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229

UPPER

UPPER(char)

SELECT UPPER('Large') "Uppercase"
     FROM DUAL;

Upper
-----
LARGE

Character Functions Returning Number Values

This section lists character functions that return number values.

ASCII 

Syntax	ASCII(char)
Purpose	Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.
Example	SELECT ASCII('Q')       FROM DUAL;   ASCII('Q') ----------         81

 

INSTR

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

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

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

INSTRB

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

LENGTH

LENGTH(char)

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

LENGTHB

LENGTHB(char)

NLSSORT

NLSSORT(char [, 'nlsparams'])

Date Functions

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

ADD_MONTHS

ADD_MONTHS(d,n)

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

Next Month
-----------
17-JAN-1981

LAST_DAY

LAST_DAY(d)

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97          8

SELECT TO_CHAR(
  ADD_MONTHS(
    LAST_DAY(hiredate),5),
     'DD-MON-YYYY') "Five months"
  FROM emp 
  WHERE ename = 'MARTIN';
 
Five months
-----------
28-FEB-1982

MONTHS_BETWEEN

MONTHS_BETWEEN(d1, d2)

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

NEW_TIME

NEW_TIME(d, z1, z2)

NEXT_DAY

ROUND

ROUND(d[,fmt])

SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR')
   "New Year" FROM DUAL;
 
New Year
---------
01-JAN-93 

SYSDATE

SYSDATE

SELECT TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW"
     FROM DUAL;
NOW
-------------------
10-29-1993 20:27:11 

TRUNC

1TRUNC(d,[fmt]) 

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92 

ROUND and TRUNC

Table 3-11 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 3-11 Date Format Models for the ROUND and TRUNC Date Functions

CC
SCC

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

IYYY
IY
IY
I

Q

MONTH
MON
MM
RM

WW

IW

W

DDD
DD
J

DAY
DY
D

HH
HH12
HH24

MI

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY. For information on this parameter, see Oracle8 Reference.

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype. This section lists the SQL conversion functions.

CHARTOROWID

CHARTOROWID(char)

SELECT ename FROM emp
   WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO');
 
ENAME
----------
LEWIS

CONVERT

CONVERT(char, dest_char_set [,source_char_set] )

SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') 
"Conversion"
  FROM DUAL;

Conversion
----------
Gross

HEXTORAW

HEXTORAW(char)

INSERT INTO graphics (raw_column)
  SELECT HEXTORAW('7D') FROM DUAL;

RAWTOHEX

RAWTOHEX(raw)

SELECT RAWTOHEX(raw_column) "Graphics"
   FROM graphics;

Graphics
--------
7D  

ROWIDTOCHAR

ROWIDTOCHAR(rowid)

SELECT ROWID 
     FROM offices
     WHERE 
     ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%';

ROWID
------------------
AAAAZ6AABAAABr1AAB

TO_CHAR, date conversion

TO_CHAR(d [, fmt [, 'nlsparams'] ])

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')
    "New date format" FROM emp
    WHERE ename = 'BLAKE';
 
New date format
------------------
May       01, 1981

TO_CHAR, number conversion

TO_CHAR(n [, fmt [, 'nlsparams'] ])

Example 2

SELECT TO_CHAR(-10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount"
     FROM DUAL;

Amount
-------------------
AusDollars10.000,00-

TO_DATE

TO_DATE(char [, fmt [, 'nlsparams'] ])

INSERT INTO bonus (bonus_date)
  SELECT TO_DATE(
    'January 15, 1989, 11:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
     FROM DUAL;

TO_MULTI_BYTE

TO_MULTI_BYTE(char)

TO_NUMBER

TO_NUMBER(char [,fmt [, 'nlsparams'] ])

UPDATE emp SET sal = sal + 
   TO_NUMBER('100.00', '9G999D99')
  WHERE ename = 'BLAKE';

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY            = ''AusDollars''
   ') "Amount"
     FROM DUAL;

    Amount
----------
      -100

TO_SINGLE_BYTE

TO_SINGLE_BYTE(char)

TRANSLATE USING

TRANSLATE(text USING {CHAR_CS | NCHAR_CS })

CREATE TABLE t1 (char_col  CHAR(20),
                  nchar_col nchar(20));
INSERT INTO t1 
  VALUES ('Hi', N'Bye');
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Bye

UPDATE t1 SET
  nchar_col = TRANSLATE(char_col USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(nchar_col USING CHAR_CS);
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Hi

UPDATE t1 SET
  nchar_col = TRANSLATE('deo' USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(N'deo' USING CHAR_CS);

CHAR_COL     NCHAR_COL
--------     ---------
deo          deo

Other Single-Row Functions

DUMP

DUMP(expr[,return_format[,start_position[,length]] ] )

SELECT DUMP('abc', 1016)
 FROM DUAL;

DUMP('ABC',1016)                          
------------------------------------------ 
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63 

SELECT DUMP(ename, 8, 3, 2) "OCTAL"
 FROM emp
 WHERE ename = 'SCOTT';

OCTAL
----------------------------
Type=1 Len=5: 117,124 

SELECT DUMP(ename, 10, 3, 2) "ASCII"
FROM emp
WHERE ename = 'SCOTT';

ASCII
----------------------------
Type=1 Len=5: 79,84

EMPTY_[B | C]LOB

EMPTY_[B|C]LOB()

INSERT INTO lob_tab1 VALUES (EMPTY_BLOB());
UPDATE lob_tab1 
  SET clob_col = EMPTY_BLOB();

BFILENAME

BFILENAME ('directory', 'filename')

INSERT INTO file_tbl
   VALUES (BFILENAME ('lob_dir1', 'image1.gif'));

GREATEST

GREATEST(expr [,expr] ...)

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

LEAST

LEAST(expr [,expr] ...)

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

NLS_CHARSET_DECL_LEN

NLS_CHARSET_DECL_LEN(bytecnt, csid)

SELECT NLS_CHARSET_DECL_LEN
  (200, nls_charset_id('ja16eucfixed')) 
   FROM DUAL; 

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED')) 
--------------------------------------------------------
                    100 

NLS_CHARSET_ID

NLS_CHARSET_ID(text)

SELECT NLS_CHARSET_ID('ja16euc') 
  FROM DUAL; 
 
NLS_CHARSET_ID('JA16EUC')
------------------------- 
                      830

SELECT NLS_CHARSET_ID('char_cs') 
  FROM DUAL; 

 NLS_CHARSET_ID('CHAR_CS')
 ------------------------- 
                         2 

SELECT NLS_CHARSET_ID('nchar_cs') 
  FROM DUAL;  

NLS_CHARSET_ID('NCHAR_CS') 
--------------------------
                         2

NLS_CHARSET_NAME

NLS_CHARSET_NAME(n)

SELECT NLS_CHARSET_NAME(2)
  FROM DUAL;

NLS_CH 
------ 
WE8DEC

NVL

NVL(expr1, expr2)

SELECT ename, NVL(TO_CHAR(COMM), 'NOT 
APPLICABLE')
   "COMMISSION" FROM emp
   WHERE deptno = 30;
 
ENAME      COMMISSION
---------- -------------------------------------
ALLEN      300
WARD       500
MARTIN     1400
BLAKE      NOT APPLICABLE
TURNER     0
JAMES      NOT APPLICABLE

UID

UID

USER

USER

SELECT USER, UID FROM DUAL;
 
USER                                  UID
------------------------------ ----------
SCOTT                                  19

USERENV

USERENV(option)

'ISDBA'

'LANGUAGE'

language_territory.characterset

'TERMINAL'

'SESSIONID'

'ENTRYID'

'LANG'

'INSTANCE'

SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

Language
-----------------------------------
AMERICAN_AMERICA.WE8DEC

VSIZE

VSIZE(expr)

SELECT ename, VSIZE (ename) "BYTES"      
  FROM emp
  WHERE deptno = 10;
 
ENAME           BYTES
---------- ----------
CLARK               5
KING                4
MILLER              6

Object Reference Functions

Object reference functions manipulate REFs-references to objects of specified object types. For more information about REFs, see Oracle8 Concepts and Oracle8 Application Developer's Guide.

DEREF

DEREF(e)

CREATE TABLE tb1(c1 NUMBER, c2 REF t1);
SELECT DEREF(c2) FROM tb1;

REFTOHEX

REFTOHEX(r)

CREATE TABLE tb1(c1 NUMBER, c2 REF t1);
SELECT REFTOHEX(c2) FROM tb1;

MAKE_REF

MAKE_REF(table, key [,key...])

CREATE TYPE t1 AS OBJECT(a NUMBER, b NUMBER);

CREATE TABLE tb1 
   (c1 NUMBER, c2 NUMBER, PRIMARY KEY(c1, c2));

CREATE VIEW v1 OF t1 WITH OBJECT OID(a, b) AS
   SELECT * FROM tb1;

SELECT MAKE_REF(v1, 1, 3) FROM DUAL;

Group Functions

Group functions return results based on groups of rows, rather than on single rows. In this way, group functions are different from single-row functions. For a discussion of the differences between group functions and single-row functions, see "SQL Functions".

Many group functions accept these options:

DISTINCT	This option causes a group function to consider only distinct values of the argument expression.
ALL	This option causes a group function to consider all values, including all duplicates.

 

For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL.

All group functions except COUNT(*) ignore nulls. You can use the NVL in the argument to a group function to substitute a value for a null.

If a query with a group function returns no rows or only rows with nulls for the argument to the group function, the group function returns null.

AVG

AVG([DISTINCT|ALL] n)

SELECT AVG(sal) "Average"
   FROM emp;

   Average
----------
2077.21429

COUNT

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

SELECT COUNT(*) "Total"
   FROM emp;
 
   Total
----------
      18

SELECT COUNT(job) "Count"
   FROM emp;
 
   Count
----------
      14

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

MAX

MAX([DISTINCT|ALL] expr)

SELECT MAX(sal) "Maximum" FROM emp;
 
   Maximum
----------
      5000

MIN

MIN([DISTINCT|ALL] expr)

SELECT MIN(hiredate) "Earliest" FROM emp;
 
Earliest
---------
17-DEC-80

STDDEV

STDDEV([DISTINCT|ALL] x)

SELECT STDDEV(sal) "Deviation"
     FROM emp;
 
 Deviation
----------
1182.50322

SUM

SUM([DISTINCT|ALL] n)

SELECT SUM(sal) "Total"
     FROM emp;
 
     Total
----------
     29081

VARIANCE

VARIANCE([DISTINCT|ALL]x)

SELECT VARIANCE(sal) "Variance"
     FROM emp;
 
Variance
----------
1389313.87

User Functions

You can write your own user functions in PL/SQL to provide functionality that is not available in SQL or SQL functions. User functions are used in a SQL statement anywhere SQL functions can be used; that is, wherever expression can occur.

For example, user functions can be used in the following:

• the select list of a SELECT command
• the condition of a WHERE clause
• CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
• the VALUES clause of an INSERT command
• the SET clause of an UPDATE command

For a complete description on the creation and use of user functions, see Oracle8 Application Developer's Guide.

Prerequisites

User functions must be created as top-level PL/SQL functions or declared with a package specification before they can be named within a SQL statement. Create user functions as top-level PL/SQL functions by using the CREATE FUNCTION statement described in CREATE FUNCTION. Specify packaged functions with a package with the CREATE PACKAGE statement described in CREATE PACKAGE.

To call a packaged user function, you must declare the RESTRICT_REFERENCES pragma in the package specification.

Privileges Required

To use a user function in a SQL expression, you must own or have EXECUTE privilege on the user function. To query a view defined with a user function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

Restrictions on User Functions

User functions cannot be used in situations that require an unchanging definition. Thus, a user function cannot:

• be used in a CHECK constraint clause of a CREATE TABLE or ALTER TABLE command
• be used in a DEFAULT clause of a CREATE TABLE or ALTER TABLE command
• contain OUT or IN OUT parameters
• update the database
• read or write package state if the function is a remote function
• use the parallelism_clause in SQL commands in the function if the function alters package state
• update variables defined in the function unless the function is a local function and is used in a SELECT list, VALUES clause of an INSERT command, or SET clause of an UPDATE command

Name Precedence

With PL/SQL, the names of database columns take precedence over the names of functions with no parameters. For example, if user SCOTT creates the following two objects in his own schema:

CREATE TABLE emp(new_sal NUMBER, ...);
CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;

then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:

SELECT new_sal FROM emp;
SELECT emp.new_sal FROM emp;

To access the function NEW_SAL, you would enter:

SELECT scott.new_sal FROM emp;

Here are some sample calls to user functions that are allowed in SQL expressions.

circle_area (radius)
payroll.tax_rate (empno)
scott.payroll.tax_rate (dependent, empno)@ny

Example

For example, to call the TAX_RATE user function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:

SELECT scott.tax_rate (ss_no, sal)
    INTO income_tax
    FROM tax_table
    WHERE ss_no = tax_id;

Naming Conventions

If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:

• Check for the PAYROLL package in the current schema.
• If a PAYROLL package is not found, look for a schema name PAYROLL that contains a top-level TAX_RATE function. If no such function is found, return an error message.
• If the PAYROLL package is found in the current schema, look for a TAX_RATE function in the PAYROLL package. If no such function is found, return an error message.

You can also refer to a stored top-level function using any synonym that you have defined for it.

Format Models

A format model is a character literal that describes the format of DATE or NUMBER data stored in a character string. You can use a format model as an argument of the TO_CHAR or TO_DATE function:

• to specify the format for Oracle to use to return a value from the database to you
• to specify the format for a value you have specified for Oracle to store in the database

Note that a format model does not change the internal representation of the value in the database.

This section describes how to use:

• number format models
• date format models
• format model modifiers

Changing the Return Format

You can use a format model to specify the format for Oracle to use to return values from the database to you.

Example 1

The following statement selects the commission values of the employees in Department 30 and uses the TO_CHAR function to convert these commissions into character values with the format specified by the number format model '$9,990.99':

SELECT ename employee, TO_CHAR(comm, '$9,990.99') commission
   FROM emp
   WHERE deptno = 30;
 
EMPLOYEE   COMMISSION
---------- ----------
ALLEN         $300.00
WARD          $500.00
MARTIN      $1,400.00
BLAKE
TURNER          $0.00
JAMES

Because of this format model, Oracle returns commissions with leading dollar signs, commas every three digits, and two decimal places. Note that TO_CHAR returns null for all employees with null in the COMM column.

Example 2

The following statement selects the date on which each employee from department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':

 SELECT ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate
    FROM emp
    WHERE deptno = 20;
 
ENAME      HIREDATE
---------- ------------------
SMITH      December 17, 1980
JONES      April 2, 1981
SCOTT      April 19, 1987
ADAMS      May 23, 1987
FORD       December 3, 1981
LEWIS      October 23, 1997

With this format model, Oracle returns the hire dates with the month spelled out (as specified by "fm" and discussed in "Format Model Modifiers"), two digits for the day, and the century included in the year.

Supplying the Correct Format

You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column. When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. For example, a value that you insert into a DATE column must be a value of the DATE datatype or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, you must use the TO_DATE function to convert the value to the DATE datatype. You must also use a format model to specify the format of the character string.

Example

The following statement updates BAKER's hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1992 05 20' to a DATE value:

UPDATE emp 
  SET hiredate = TO_DATE('1992 05 20','YYYY MM DD') 
  WHERE ename = 'BLAKE'; 

Number Format Models

You can use number format models

• in the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype
• in the TO_NUMBER function to translate a value of CHAR or VARCHAR2 datatype to NUMBER datatype

All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).

Number Format Elements

A number format model is composed of one or more number format elements. Table 3-12 lists the elements of a number format model. Examples are shown in Table 3-13.

• If a number format model does not contain the MI, S, or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.
• A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,).
• A number format model must not begin with a comma (,).
• A group separator or comma cannot appear to the right of a decimal character or period in a number format model.

Table 3-12 Number Format Elements

Element	Example	Description
9	9999	Return value with the specified number of digits with a leading space if positive.   Return value with the specified number of digits with a leading minus if negative.   Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
0	0999   9990	Return leading zeros.   Return trailing zeros.
$	$9999	Return value with a leading dollar sign.
B	B9999	Return blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0's in the format model).
MI	9999MI	Return negative value with a trailing minus sign "-".   Return positive value with a trailing blank.
S	S9999   9999S	Return negative value with a leading minus sign "-".   Return positive value with a leading plus sign "+".   Return negative value with a trailing minus sign "-".   Return positive value with a trailing plus sign "+".
PR	9999PR	Return negative value in <angle brackets>.   Return positive value with a leading and trailing blank.
D	99D99	Return a decimal character (that is, a period ".") in the specified position.
G	9G999	Return a group separator in the position specified.
C	C999	Return the ISO currency symbol in the specified position.
L	L999	Return the local currency symbol in the specified position.
, (comma)	9,999	Return a comma in the specified position.
. (period)	99.99	Return a decimal point (that is, a period ".") in the specified position.
V	999V99	Return a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the "V".
EEEE	9.9EEEE	Return a value using in scientific notation.
RN   rn	RN	Return a value as Roman numerals in uppercase.   Return a value as Roman numerals in lowercase.   Value can be an integer between 1 and 3999.
FM	FM90.9	Return a value with no leading or trailing blanks.

   

Example

Table 3-13 shows the results of the following query for different values of number and 'fmt':

SELECT TO_CHAR(number, 'fmt')
  FROM DUAL

Table 3-13 Results of Example Number Conversions

The MI and PR format elements can appear only in the last position of a number format model. The S format element can appear only in the first or last position of a number format model.

The characters returned by some of these format elements are specified by initialization parameters. Table 3-14 lists these elements and parameters.

Table 3-14 Number Format Element Values Determined by Initialization Parameters

You can specify the characters returned by these format elements implicitly using the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Reference.

You can change the characters returned by these format elements for your session with the ALTER SESSION command. You can also change the default date format for your session with the ALTER SESSION command. For information, see ALTER SESSION.

Date Format Models

You can use date format models

• in the TO_CHAR function to translate a DATE value that is in a format other than the default date format
• in the TO_DATE function to translate a character value that is in a format other than the default date format

Default Date Format

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Reference.

You can change the default date format for your session with the ALTER SESSION command. For information, see ALTER SESSION.

Maximum Length

The total length of a date format model cannot exceed 22 characters.

Date Format Elements

A date format model is composed of one or more date format elements as listed in Table 3-15. For input format models, format items cannot appear twice, and format items that represent similar information cannot be combined. For example, you cannot use 'SYYYY' and 'BC' in the same format string. Only some of the date format elements can be used in the TO_DATE function as noted in Table 3-15.

Table 3-15 Date Format Elements

-
/
,
.
;
:
'text'

AD
A.D.

AM
A.M.

BC
B.C.

CC
SCC

D

DAY

DD

DDD

DY

E

EE

HH

HH12

HH24

IW

IYY
IY
I

IYYY

J

MI

MM

MON

MONTH

PM
P.M.

Q

RM

RR

RRRR

SS

SSSSS

WW

W

Y,YYY

YEAR
SYEAR

YYYY
SYYYY

YYY
YY
Y

Oracle returns an error if an alphanumeric character is found in the date string where punctuation character is found in the format string. For example:

TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')

returns an error.

Date Format Elements and National Language Support

The functionality of some date format elements depends on the country and language in which you are using Oracle. For example, these date format elements return spelled values:

• MONTH
• MON
• DAY
• DY
• BC or AD or B.C. or A.D.
• AM or PM or A.M or P.M.

The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE or implicitly with the initialization parameter NLS_LANGUAGE. The values returned by the YEAR and SYEAR date format elements are always in English.

The date format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

For information on these initialization parameters, see Oracle8 Reference.

ISO Standard Date Format Elements

Oracle calculates the values returned by the date format elements IYYY, IYY, IY, I, and IW according to the ISO standard. For information on the differences between these values and those returned by the date format elements YYYY, YYY, YY, Y, and WW, see the discussion of national language support in Oracle8 Reference.

The RR Date Format Element

The RR date format element is similar to the YY date format element, but it provides additional flexibility for storing date values in other centuries. The RR date format element allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. It will also allow you to store 20th century dates in the 21st century in the same way if necessary.

If you use the TO_DATE function with the YY date format element, the date value returned is always in the current century. If you use the RR date format element instead, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Table 3-16 summarizes the behavior of the RR date format element.

Table 3-16 The RR Date Element Format

The following example demonstrates the behavior of the RR date format element.

Example 1

Assume these queries are issued between 1950 and 1999:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year"
     FROM DUAL;

Year
----
1995

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";
     FROM DUAL; 

Year
----
2017

Example 2

Assume these queries are issued between 2000 and 2049:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year";
  FROM DUAL; 

Year
----
1995 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";
     FROM DUAL; 

Year
----
2017

Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values after the turn of the century.

Date Format Element Suffixes

Table 3-17 lists suffixes that can be added to date format elements:

Table 3-17 Date Format Element Suffixes

When you add one of these suffixes to a date format element, the return value is always in English.

Capitalization of Date Format Elements

Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.

Punctuation and Character Literals in Date Format Models

You can also include these characters in a date format model:

• punctuation such as hyphens, slashes, commas, periods, and colons
• character literals, enclosed in double quotation marks

These characters appear in the return value in the same location as they appear in the format model.

Format Model Modifiers

You can use the FM and FX modifiers in format models for the TO_CHAR function to control blank padding and exact format checking.

A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.

FM

"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:

• In a date format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading and trailing zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, because there is no blank padding, the length of the return value may vary
• In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.

FX

"Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE function:

• Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
• The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.
• Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes. 

When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.

If any portion of the character argument violates any of these conditions, Oracle returns an error message.

Example 1

The following statement uses a date format model to return a character expression:

SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR
   (SYSDATE, 'Month')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides" 
    FROM DUAL; 

Ides 
------------------ 
3RD of April, 1995

Note that the statement above also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:

SELECT TO_CHAR(SYSDATE, 'DDTH')||' of '||
   TO_CHAR(Month, YYYY') "Ides"
   FROM DUAL; 

Ides 
----------------------- 
03RD of April    , 1995

Example 2

The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:

SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special') "Menu"
     FROM DUAL; 

Menu 
----------------- 
Tuesday's Special 

Two consecutive single quotation marks can be used for the same purpose within a character literal in a format model.

Example 3

Table 3-18 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX:

UPDATE table 
  SET date_column = TO_DATE(char, 'fmt');

Table 3-18 Matching Character Data and Format Models with the FX Format Model Modifier

'15/ JAN /1993'

'DD-MON-YYYY'

' 15! JAN % /1993'

'DD-MON-YYYY'

'15/JAN/1993'

'FXDD-MON-YYYY'

'15-JAN-1993'

'FXDD-MON-YYYY'

'1-JAN-1993'

'FXDD-MON-YYYY'

'01-JAN-1993'

'FXDD-MON-YYYY'

'1-JAN-1993'

'FXFMDD-MON-YYYY'