Oracle7 Server SQL Reference Manual | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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 operator, the result is always null. The only operator that does not follow this rule is concatenation (||).
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.
Highest Precedence | |
Unary + - arithmetic operators | PRIOR Operator |
* / arithmetic operators | |
Binary = - arithmetic operators | || character operators |
All comparison operators | |
NOT logical operator | |
AND logical operator | |
OR logical operator | |
Table 3 - 1. SQL Operator Precedence | |
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.
Example
In the following expression multiplication has a higher precedence than addition, so Oracle7 first multiplies 2 by 3 and then adds the result to 1.
1+2*3
Operator | Purpose | Example |
+ - | Denotes a positive or negative expression. These are unary operators. | SELECT * FROM orders WHERE qtysold = -1 SELECT * FROM emp WHERE -sal < 0 |
* / | Multiplies, divides. These are binary operators. | UPDATE emp SET sal = sal * 1.1 |
+ - | Adds, subtracts. These are binary operators. | SELECT sal + comm FROM emp WHERE SYSDATE - hiredate > 365 |
Table 3 - 2. Arithmetic Operators | ||
Operator | Purpose | Example |
|| | Concatenates character strings. | SELECT 'Name is ' || ename FROM emp |
Table 3 - 3. Character Operators | ||
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 Oracle7 environment. Because it may be difficult or impossible to control translation performed by operating system or network utilities, the CONCAT character function is provided as an alternative to the vertical bar operator. Its use is recommended in applications that will be moved to environments with differing character sets.
Although Oracle7 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 only result from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle7. 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
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 null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.
where:
char1
is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.
NOT
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
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%'. Oracle7 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 Oracle7 matches with different characters in the value:
UPPER(ename) LIKE 'SM%'
Pattern Matching on Indexed Columns When LIKE is used to search an indexed column for a pattern, Oracle7 can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle7 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 Oracle7 cannot scan the index.
Example I
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
Since case is significant, ENAME values beginning with "Ma," "ma," and "mA" make the condition FALSE.
Example II
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 Oracle7 interprets this character literally in the pattern, rather than as a special pattern matching character.
Example III
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 Oracle7 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 only be TRUE if both operands have the same length.
Example IV
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 Oracle7 blank-pads CHAR values, the value of F is blank-padded to 6 bytes. V is not blank-padded and has length 4. Table 3 - 5 shows conditions that evaluate to TRUE and FALSE.
Operator | Function | Example |
NOT | Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN | SELECT * FROM emp WHERE NOT (job IS NULL) SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000) |
AND | Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN. | SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10 |
OR | Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN. | SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10 |
Table 3 - 5. Logical Operators | ||
SELECT * FROM emp WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') AND sal > 1000
NOT | TRUE | FALSE | UNKNOWN |
FALSE | TRUE | UNKNOWN | |
Table 3 - 6. NOT Truth Table | |||
AND | TRUE | FALSE | UNKNOWN |
TRUE | TRUE | FALSE | UNKNOWN |
FALSE | FALSE | FALSE | FALSE |
UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
Table 3 - 7. AND Truth Table | |||
OR | TRUE | FALSE | UNKNOWN |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | UNKNOWN |
UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
Table 3 - 8. OR Truth Table | |||
Operator | Returns |
UNION | All rows selected by either query. |
UNION ALL | All rows selected by either query, including all duplicates. |
INTERSECT | All distinct rows selected by both queries. |
MINUS | All distinct rows selected by the first query but not the second. |
Table 3 - 9. 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:
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 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 ---------- ------- -------- SPARKPLUS 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
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
SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2
PART ---------- TAILPIPE
MINUS Example
SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2 PART ---------- SPARKPLUG FUEL PUMP
Operator | Purpose | Example |
(+) | Indicates that the preceding column is the outer join column in a join. See the section "Outer Joins" on page 4 - 425. | SELECT ename, dname FROM emp, dept WHERE dept.deptno = emp.deptno(+) |
PRIOR | Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured, query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between parent and child rows. You can also use this operator in other parts of a SELECT statement that performs a hierarchical query. The PRIOR operator is a unary operator and has the same precedence as the unary + and - arithmetic operators. See the section "Hierarchical Queries" ![]() | SELECT empno, ename, mgr FROM emp CONNECT BY PRIOR empno = mgr |
Table 3 - 10. Other SQL Operators | ||
function(argument, argument, ...)
If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle7 implicitly converts the argument to the expected datatype before performing the SQL function. See the section "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 the syntax diagrams for SQL functions, arguments are indicated with their datatypes following the conventions described in the Preface of this manual.
SQL functions are of these general types:
Single row functions can appear in select lists (provided 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, Oracle7 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 either expressions from the GROUP BY clause, expressions containing group functions, or constants. Oracle7 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, Oracle7 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 section "GROUP BY Clause" and the section "HAVING Clause"
.
Purpose
Returns the absolute value of n
Example
SELECT ABS(-15) "Absolute" FROM DUAL Absolute ---------- 15
Purpose
Returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
Example
SELECT ACOS(.3) "Arc_Cosine" FROM DUAL Arc_Cosine ---------- 1.26610367
Purpose
Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.
Example
SELECT ASIN(.3) "Arc_Sine" FROM DUAL Arc_Sine ---------- .304692654
Purpose
Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.
Example
SELECT ATAN(.3) "Arc_Tangent" FROM DUAL Arc_Tangent _---------- .291456794
Purpose
Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of -pi to pi, depending on the signs of x and y, and are expressed in radians. Atan2(x,y) is the same as atan2(x/y)
Example
SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL Arc_Tangent2 ------------ .982793723
Purpose
Returns smallest integer greater than or equal to n.
Example
SELECT CEIL(15.7) "Ceiling" FROM DUAL Ceiling ---------- 16
Purpose
Returns the cosine of n (an angle expressed in radians).
Example
SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL Cosine of 180 degrees --------------------- -1
Purpose
Returns the hyperbolic cosine of n.
Example
SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL Hyperbolic cosine of 0 ---------------------- 1
Purpose
Returns e raised to the nth power; e = 2.71828183 ...
Example
SELECT EXP(4) "e to the 4th power" FROM DUAL e to the 4th power ------------------ 54.59815
Purpose
Returns largest integer equal to or less than n.
Example
SELECT FLOOR(15.7) "Floor" FROM DUAL Floor ---------- 15
Purpose
Returns the natural logarithm of n, where n is greater than 0.
Example
SELECT LN(95) "Natural log of 95" FROM DUAL Natural log of 95 ----------------- 4.55387689
Purpose
Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.
Example
SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL Log base 10 of 100 ------------------ 2
Purpose
Returns remainder of m divided by n. Returns m if n is 0.
Example
SELECT MOD(11,4) "Modulus" FROM DUAL Modulus ---------- 3
Note
This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)
Example
The following statement illustrates the difference between the MOD function and the classical modulus:
SELECT m, n, MOD(m, n), m - n * FLOOR(m/n) "Classical Modulus" FROM test_mod_table M N MOD (M,N) Classical Modulus --- ---- -------- --------- ------- 11 4 3 -11 4 -3 1 11 -4 3 -1 -11 -4 -3 -3
Purpose
Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.
Example
SELECT POWER(3,2) "Raised" FROM DUAL Raised ---------- 9
Purpose
Returns n rounded to m places 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
SELECT ROUND(15.193,1) "Round" FROM DUAL Round ---------- 15.2
Example
SELECT ROUND(15.193,-1) "Round" FROM DUAL Round ---------- 20
Purpose
If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1.
Example
SELECT SIGN(-15) "Sign" FROM DUAL Sign ---------- -1
Purpose
Returns the sine of n (an angle expressed in radians).
Example
SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL Sine of 30 degrees ------------------ .5
Purpose
Returns the hyperbolic sine of n.
Example
SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL Hyperbolic sine of 1 -------------------- 1.17520119
Purpose
Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.
Example
SELECT SQRT(26) "Square root" FROM DUAL Square root ----------- 5.09901951
Purpose
Returns the tangent of n (an angle expressed in radians).
Example
SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL Tangent of 135 degrees ---------------------- -1
Purpose
Returns the hyperbolic tangent of n.
Example
SELECT TANH(.5) "Hyperbolic tangent of .5" FROM DUAL Hyperbolic tangent of .5 ------------------------ .462117157
Purpose
Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
Examples
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL Truncate ---------- 15.7 SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL Truncate ---------- 10
Purpose
Returns the character having the binary equivalent to n in the database character set.
Example
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL Dog --- CAT
Purpose
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see the section "Character" .
Example
This example uses nesting to concatenate three character strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job" FROM emp WHERE empno = 7900 Job ------------------------- JAMES is a CLERK
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 Capitals -------- The Soap
Purpose
Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).
Example
SELECT LOWER('MR. SAMUEL HILLHOUSE') "Lowercase" FROM DUAL Lowercase -------------------- mr. samuel hillhouse
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('Page 1',15,'*.') "LPAD example" FROM DUAL LPAD example --------------- *.*.*.*.*Page 1
Purpose
Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle7 begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
Example
SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL LTRIM example ------------- Xxy LAST WORD
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. The value of 'nlsparams' can have this form:
'NLS_SORT = sort'
where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparams', this function uses the default sort sequence for your session. For information on sort sequences, see Oracle7 Server Reference.
Example
SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "Capitalized" FROM DUAL Capital ------- IJsland
Purpose
Returns char, with all letters lowercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.
Example
SELECT NLS_LOWER('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL Lower ----- cittą
Purpose
Returns char, with all letters uppercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.
Example
SELECT NLS_UPPER('gro?e', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL Upper ----- GROSS
Purpose
Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single character, one to one, substitution. REPLACE allows 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 Changes -------------- BLACK and BLUE
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' RPAD example ------------ TURNERababab
Purpose
Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.
Example
SELECT RTRIM('TURNERyxXxy','xy') "RTRIM e.g." FROM DUAL RTRIM e.g --------- TURNERyxX
Purpose
Returns a character string containing the phonetic representation of char. This function allows you to compare words that are spelled differently, but sound alike in English.
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
Example
SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE') ENAME ---------- SMITH
Purpose
Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle7 counts from the beginning of char to find the first character. If m is negative, Oracle7 counts backwards from the end of char. If n is omitted, Oracle7 returns all characters to the end of char. If n is less than 1, a null is returned.
Floating point numbers passed as arguments to substr are automatically converted to integers.
Example
SELECT SUBSTR('ABCDEFG',3.1,4) "Subs" FROM DUAL Subs ---- CDEF SELECT SUBSTR('ABCDEFG',-5,4) "Subs" FROM DUAL Subs ---- CDEF
Purpose
The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.
Floating point numbers passed as arguments to substrb are automatically converted to integers.
Example
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL Sub --- CD
Purpose
Returns char with all occurrences of each character in from replaced by its corresponding character in to. 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 all characters in from from the return value. Oracle7 interprets the empty string as null, and if this function has a null argument, it returns null.
Examples
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012...9' are translated to '9':
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL Translate example ----------------- 9XXX999
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') "Translate example" FROM DUAL Translate example ----------------- 2229
Purpose
Returns char, with all letters uppercase. The return value has the same datatype as the argument char.
Example
SELECT UPPER('Large') "Uppercase" FROM DUAL Uppercase --------- LARGE
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. 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
Purpose
Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle7 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 Oracle7 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.
Examples
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
Purpose
The same as INSTR, except that n and the return value are expressed in bytes, rather than in 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 Instring in bytes ----------------- 27
Purpose
Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.
Example
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL Length in characters -------------------- 7
Purpose
Returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.
Example
Assume a double-byte database character set:
SELECT LENGTH('CANDIDE') "Length in bytes" FROM DUAL Length in bytes --------------- 14
Purpose
Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form
'NLS_SORT = sort'
where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. For information on sort sequences, see the "National Language Support" chapter of Oracle7 Server Reference..
Example
This function can be used to specify comparisons based on a linguistic sort sequence rather on the binary value of a string:
SELECT * FROM emp WHERE NLSSORT(ename,'NLS_SORT = German') > NLSSORT('B','NLS_SORT = German')
Purpose
Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.
Example
SELECT TO_CHAR( ADD_MONTHS(hiredate,1), 'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH' Next Month ----------- 17-JAN-1981
Purpose
Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.
Example
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL SYSDATE Last Days Left --------- --------- ---------- 10-APR-95 30-APR-95 20 SELECT TO_CHAR( ADD_MONTHS( LAST_DAY(hiredate),5), 'DD-MON-YYYY') "Five months" FROM emp WHERE ename = 'MARTIN' Five months ----------- 28-FEB-1982
SELECT TO_CHAR(ADD_MONTHS(hiredate,1),
'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH' Next month ----------- 17-JAN-1981
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 Oracle7 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 Months ---------- 1.03225806
Purpose
Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:
AST ADT | Atlantic Standard or Daylight Time |
BST BDT | Bering Standard or Daylight Time |
CST CDT | Central Standard or Daylight Time |
EST EDT | Eastern Standard or Daylight Time |
GMT | Greenwich Mean Time |
HST HDT | Alaska-Hawaii Standard Time or Daylight Time. |
MST MDT | Mountain Standard or Daylight Time |
NST | Newfoundland Standard Time |
PST PDT | Pacific Standard or Daylight Time |
YST YDT | Yukon Standard or Daylight Time |
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
This example returns the date of the next Tuesday after March 15, 1992.
SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY" FROM DUAL NEXT DAY --------- 17-MAR-92
Purpose
Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day.
For details on ROUND and TRUNC, see the section "ROUND and TRUNC" .
Example
SELECT ROUND(TO_DATE('27-OCT-92'),'YEAR') "FIRST OF THE YEAR" FROM DUAL FIRST OF THE YEAR ----------------- 01-JAN-93
Purpose
Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.
Example
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') NOW FROM DUAL NOW ------------------- 10-29-1993 20:27:11.
Purpose
Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See the next section "ROUND and TRUNC."
Example
SELECT TRUNC(TO_DATE('27-OCT-92', 'DD-MON-YY'), 'YEAR') "First Of The Year" FROM DUAL FIRST OF THE YEAR ----------------- 01-JAN-92
Format Model | Rounding or Truncating Unit |
CC SCC | Century |
SYYYY YYYY YEAR SYEAR YYY YY Y | Year (rounds up on July 1) |
IYYY IY IY I | ISO Year |
Q | Quarter (rounds up on 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 |
Table 3 - 11. Date Format Models for the ROUND and TRUNC Date Functions | |
Purpose
Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.
Example
SELECT ename FROM emp WHERE ROWID = CHARTOROWID('0000000F.0003.0002') ENAME ----- SMITH
Purpose
Converts a character string from one character set to another.
The char argument is the value to be converted.
The dest_char_set argument is the name of the character set to which char is converted.
The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.
Common character sets include:
US7ASCII | US 7-bit ASCII character set |
WE8DEC | DEC West European 8-bit character set |
WE8HP | HP West European Laserjet 8-bit character set |
F7DEC | DEC French 7-bit character set |
WE8EBCDIC500 | IBM West European EBCDIC Code Page 500 |
WE8PC850 | IBM PC Code Page 850 |
WE8ISO8859P1 | ISO 8859-1 West European 8-bit character set |
SELECT CONVERT('Groß', 'WE8HP', 'WE8DEC') "Conversion" FROM DUAL Conversion ---------- Groß
Purpose
Converts char containing hexadecimal digits to a raw value.
Example
INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL
Purpose
Converts raw to a character value containing its hexadecimal equivalent.
Example
SELECT RAWTOHEX(raw_column) "Graphics" FROM graphics Graphics -------- 7D
Purpose
Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.
Example
SELECT ROWID FROM graphics WHERE ROWIDTOCHAR(ROWID) LIKE '%F38%' ROWID ------------------ 00000F38.0001.0001
Purpose
Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see the section "Format Models" .
The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
If you omit nlsparams, this function uses the default date language for your session.
Example
SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY') "New date format" FROM emp WHERE ename = 'SMITH' New date format ------------------------------- December 17, 1980
Purpose
Converts label of MLSLABEL datatype to a value of VARCHAR2 datatype, using the optional label format fmt. If you omit fmt, label is converted to a VARCHAR2 value in the default label format.
For more information on this function, see Trusted Oracle7 Server Administrator's Guide.
Purpose
Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see the section "Format Models" .
The 'nlsparams' specifies these characters that are returned by number format elements:
This argument can have this form:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.
Example I
SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL Amount -------------------- $10,000.00-
Note how the output above is blank padded to the left of the currency symbol.
Example II
SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL Amount -------------------- AusDollars10.000,00-
Purpose
Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. For information on date formats, see the section "Format Models" .
The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion.
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.
For information on date formats, see page 3 - 64.
Example
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
Purpose
Converts char, a value of datatype CHAR or VARCHAR2 containing a label in the format specified by the optional parameter fmt, to a value of MLSLABEL datatype. If you omit fmt, char must be in the default label format. For more information on this function, see Trusted Oracle7 Server Administrator's Guide.
Purpose
Returns char with all of its single-byte characters converted to their corresponding multi-byte characters. Any single-byte characters in char that have no multi-byte equivalents appear in the output string as single-byte characters. This function is only useful if your database character set contains both single-byte and multi-byte characters.
Purpose
Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
Example
UPDATE emp SET sal = sal + TO_NUMBER('100.00', '9G999D99') WHERE ename = 'BLAKE'
The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for number conversion.
Example
SELECT TO_NUMBER('-AusDollars100','L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL Amount ---------- -100
Purpose
Returns char with all of its multi-byte character converted to their corresponding single-byte characters. Any multi-byte characters in char that have no single-byte equivalents appear in the output as multi-byte characters. This function is only useful if your database character set contains both single-byte and multi-byte characters.
Purpose
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The argument return_format specifies the format of the return value and can have any of these values:
8 returns result in octal notation.
10 returns result in decimal notation.
16 returns result in hexadecimal notation.
17 returns result as single characters.
The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.
If expr is null, this function returns 'NULL'.
For the datatype corresponding to each code, see Table 2 - 1 .
Examples
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
SELECT DUMP(ename, 16, 3, 2) "HEX"
FROM emp WHERE ename = 'SCOTT' HEX ---------------------------- Type=1 Len=5: 4f,54 SELECT DUMP(ename, 17, 3, 2) "CHAR" FROM emp WHERE ename = 'SCOTT' CHAR ----------------------- Type=1 Len=5: O,T
Purpose
Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first exprs before the comparison. Oracle7 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 GREATEST -------- HARRY
Purpose
Returns the greatest lower bound of the list of labels. Each label must either have datatype MLSLABEL or RAW MLSLABEL or be a quoted literal in the default label format. The return value has datatype RAW MLSLABEL.
For the definition of greatest lower bound and examples of this function, see Trusted Oracle7 Server Administrator's Guide.
Purpose
Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle7 compares the exprs using non-padded comparison semantics. If the value returned by this function is character data, its datatype is always VARCHAR2.
Example
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL LEAST ------ HAROLD
Purpose
Returns the least upper bound of the list of labels. Each label must have datatype MLSLABEL or be a quoted literal in the default label format. The return value has datatype RAW MLSLABEL. For the definition of least upper bound and examples of this function, see Trusted Oracle7 Server Administrator's Guide.
Purpose
If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle7 converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data in which case the return value's datatype is VARCHAR2.
Example
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
Purpose
Returns an integer that uniquely identifies the current user.
Purpose
Returns the current Oracle7 user with the datatype VARCHAR2. Oracle7 compares values of this function with blank-padded comparison semantics.
In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint.
Example
SELECT USER, UID FROM DUAL USER UID ------------------------------ ---------- OPS$BQUIGLEY 46
Purpose
Returns information of VARCHAR2 datatype about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. The argument option can have any of these values:
'OSDBA' returns 'TRUE' if you currently have the OSDBA role enabled and 'FALSE' if you do not.
'LABEL' returns your current session label. This option is only applicable for Trusted Oracle7. For more information on this option, see Trusted Oracle7 Server Administrator's Guide.
'LANGUAGE' returns the language and territory currently used by your session along with the database character set in this form: language_territory.characterset
'CLIENT_INFO' Returns the value of the client_info field of the current session, as the last value set by the dbms_application_info.set_client_info procedure.
'LANG' Returns the ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
Example
SELECT USERENV('LANGUAGE') "Language" FROM DUAL Language ---------------------------------------------------- AMERICAN_AMERICA.WE8DEC
Purpose
Returns the number of bytes in the internal representation of expr. If expr is null, this function returns null.
Example
SELECT ename, VSIZE(ename) "BYTES" FROM emp WHERE deptno = 10 ENAME BYTES ---------- --------- CLARK 5 KING 4 MILLER 6
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.
Purpose
Returns average value of n.
Example
SELECT AVG(sal) "Average" FROM emp Average ---------- 2077.21429
Purpose
Returns the number of rows in the query.
If you specify expr, 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.
Examples
SELECT COUNT(*) "Total" FROM emp Total ---------- 18
SELECT COUNT(job) "Count" FROM emp Count ---------- 14
SELECT COUNT(DISTINCT job) "Jobs" FROM emp Jobs ---------- 5
Purpose
Returns the greatest lower bound of label. For the definitions of greatest lower bound and example usage, see Trusted Oracle7 Server Administrator's Guide.
Purpose
Returns the least upper bound of label.
The return values have datatype MLSLABEL. For the definitions of greatest least upper bound and example usage, see Trusted Oracle7 Server Administrator's Guide.
Purpose
Returns maximum value of expr.
Example
SELECT MAX(sal) "Maximum" FROM emp Maximum ---------- 5004
Purpose
Returns minimum value of expr.
Example
SELECT MIN(hiredate) "Minimum Date" FROM emp Minimum Date ------------ 17-DEC-80
Note
The DISTINCT and ALL options have no effect on the MAX and MIN functions.
Purpose
Returns standard deviation of x, a number. Oracle7 calculates the standard deviation as the square root of the variance defined for the VARIANCE group function.
Example
SELECT STDDEV(sal) "Deviation" FROM emp Deviation ---------- 1182.50322
Purpose
Returns sum of values of n.
Example
SELECT SUM(sal) "Total" FROM emp Total ---------- 29081
Purpose
Returns variance of x, a number. Oracle7 calculates the variance of x using this formula:
where:
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 Variance ---------- 1389313.87
For example, user functions can be used in the following:
To call a packaged user function, you must declare the RESTRICT_REFERENCES pragma in the package specification.
CREATE TABLE emp(new_sal NUMBER, ...) CREATE FUNCTION new_sal RETURN NUMBER IS ,,,;
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;
Example I
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;
Example II
Listed below are 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
This section describes how to use:
Example I
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, Oracle7 returns the commissions with leading dollar signs, commas every three digits, and two decimal places. Note that the TO_CHAR function returns null for all employees with null in the COMM column.
Example II
The following statement selects the dates that 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
With this format model, Oracle7 returns the hire dates with the month spelled out, two digits for the day, and the century included in the year.
Example III
The following statement updates JONES' 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 = 'JONES'
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.
The characters returned by some of these format elements are specified by initialization parameters. Table 3 - 13 lists these elements and parameters.
Element | Description | Initialization Parameter |
D | Decimal character | NLS_NUMERIC_CHARACTER |
G | Group separator | NLS_NUMERIC_CHARACTER |
C | ISO currency symbol | NLS_ISO_CURRENCY |
L | Local currency symbol | NLS_CURRENCY |
Table 3 - 13. Number Format Element Values Determined by Initialization Parameters | ||
You can also change the characters returned by these format elements for your session with the ALTER SESSION command. For information on this command, see page 4 - 55.
For information on these parameters, see Oracle7 Server Reference. You can also change the default date format for your session with the ALTER SESSION command. For information on this command, see page 4 - 55.
For information on these parameters, see Oracle7 Server Reference. You can also change the default date format for your session with the ALTER SESSION command. For information on this command, see page 4 - 55.
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 Oracle7 Server Reference.
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 - 15 summarizes the behavior of the RR date format element.
If the specified two-digit year is | |||
0 - 49 | 50 - 99 | ||
If the last two digits of the current year are: | 0-49 | The return date is in the current century. | The return date is in the century before the current one. |
50-99 | The return date is in the century after the current one. | The return date is in the current century. | |
Table 3 - 15. The RR Date Element Format | |||
Example IV
Assume these queries are issued before the year 2000:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "4-digit year" FROM DUAL 4-digit year ------------ 1995
SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "4-digit year" FROM DUAL 4-digit year ------------ 2017
Assume these queries are issued in the year 2000 or after:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "4-digit year" FROM DUAL 4-digit year ------------ 1995 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "4-digit year" FROM DUAL 4-digit 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.
Suffix | Meaning | Example Element | Example Value |
TH | Ordinal Number | DDTH | 4TH |
SP | Spelled Number | DDSP | FOUR |
SPTH or THSP | Spelled, ordinal number | DDSPTH | FOURTH |
Table 3 - 16. Date Format Element Suffixes | |||
Note: Date suffixes are only valid on output and cannot be used to insert a date into the database.
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 re-enabled 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:
Example V
Table 3 - 17 shows the results of the following query for different values of number and 'fmt':
SELECT TO_CHAR(number, 'fmt') FROM dual
number | 'fmt' | Result |
-1234567890 | 9999999999S | '1234567890-' |
0 | 99.99 | ' 0.00' |
+0.1 | 99.99 | ' .10' |
-0.2 | 99.99 | ' -.20' |
0 | 90.99 | ' 0.00' |
+0.1 | 90.99 | ' .10' |
-0.2 | 90.99 | ' -0.20' |
0 | 9999 | ' 0' |
1 | 9999 | ' 1' |
0 | B9999 | ' ' |
1 | B9999 | ' 1' |
0 | B90.99 | ' ' |
+123.456 | 999.999 | ' 123.456' |
-123.456 | 999.999 | '-123.456' |
+123.456 | FM999.009 | '123.456' |
+123.456 | 9.9EEEE | ' 1.2E+02' |
+1E+123 | 9.9EEEE | ' 1.0E+123' |
+123.456 | FM9.9EEEE | '1.23E+02' |
+123.45 | FM999.009 | '123.45' |
+123.0 | FM999.009 | '123.00' |
+123.45 | L999.99 | ' $123.45' |
+123.45 | FML99.99 | '$123.45' |
+1234567890 | 9999999999S | '1234567890+' |
Table 3 - 17. Results of Example Number Conversions | ||
The following statement uses a date format model to return a character expression that contains the character literal "the" and a comma.
SELECT TO_CHAR(SYSDATE, 'fmDDTH "of" Month, YYYY') Ides FROM DUAL Ides ------------------ 3RD of April, 1995
Note that the following statement also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:
SELECT TO_CHAR(SYSDATE, 'DDTH "of" Month, YYYY') Ides FROM DUAL Ides
----------------------- 03RD of April , 1995
You can include a single quotation mark in the return value by placing two consecutive single quotation marks in the format model.
Example VII
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 also be used for the same purpose within a character literal in a format model.
Example VIII
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')
char | 'fmt' | Match or Error? |
'15/ JAN /1993' | 'DD-MON-YYYY' | Match |
' 15! JAN % /1993' | 'DD-MON-YYYY' | Match |
'15/JAN/1993' | 'FXDD-MON-YYYY' | Error |
'15-JAN-1993' | 'FXDD-MON-YYYY' | Match |
'1-JAN-1993' | 'FXDD-MON-YYYY' | Error |
'01-JAN-1993' | 'FXDD-MON-YYYY' | Error |
'1-JAN-1993' | 'FXFMDD-MON-YYYY' | Match |
Table 3 - 18. Matching Character Data and Format Models with the FX Format Model Modifier | ||
In addition to the schema of a user, schema can also be "PUBLIC" (double quotation marks required), in which case it must qualify a public synonym for a table, view, or snapshot. Qualifying a public synonym with "PUBLIC" is only supported in Data Manipulation Language commands, not Data Definition Language commands.
The pseudocolumn can be either LEVEL, ROWID, or ROWNUM. You can only use a pseudocolumn with a table, rather than with a view or snapshot. For more information on pseudocolumns, see the section "Pseudocolumns" .
ROWLABEL is a column automatically created by Trusted Oracle7 in every table in the database. If you are using Trusted Oracle7, the expression ROWLABEL returns the row's label. If you are not using Trusted Oracle7, the expression ROWLABEL always returns NULL. For information on using labels and ROWLABEL, see Trusted Oracle7 Server Administrator's Guide.
Examples
emp.ename 'this is a text string' 10
Examples
:employee_name INDICATOR :employee_name_indicator_var
:department_location
For information on SQL functions, see the section "SQL Functions" .
Examples
LENGTH('BLAKE') ROUND(1234.567*43) SYSDATE
For information on user functions, see the section "User Functions" .
Examples
circle_area(radius) payroll.tax_rate(empno) scott.payrol.tax_rate(dependents, empno)@ny
Note that some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within a group function.
Examples
('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY')
To evaluate this expression, Oracle7 compares expr to each search value one by one. If expr is equal to a search, Oracle7 returns the corresponding result. If no match is found, Oracle7 returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle7 compares them using non-padded comparison semantics. For information on these semantics, see the section "Datatype Comparison Rules" .
The search, result, and default values can be derived from expressions. Oracle7 evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle7 never evaluates a search if a previous search is equal to expr.
Oracle7 automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle7 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 Oracle7 converts the return value to the datatype VARCHAR2. For information on datatype conversion, see the section "Data Conversion" .
In a DECODE expression, Oracle7 considers two nulls to be equivalent. If expr is null, Oracle7 returns the result of the first search that is also null.
The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.
Example
This expression decodes the value DEPTNO. If DEPTNO is 10, the expression evaluates to 'ACCOUNTING'; if DEPTNO is 20, it evaluates to 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.
DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')
An expression list can contain up to 254 expressions.
Examples
(10, 20, 40)
('SCOTT', 'BLAKE', 'TAYLOR')
(LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69)
This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):
2*2
The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:
TO_CHAR(TRUNC(SYSDATE+7))
You can use expressions in any of these places:
SET ename = 'smith'
This SET clause has the expression LOWER(ENAME) instead of the quoted string 'smith':
SET ename = LOWER(ename)
For information on comparison operators, see the section "Comparison Operators" .
For the syntax of a subquery, see page 4 - 431.
The following is a simple condition that always evaluates to TRUE:
1 = 1
The following is a more complex condition that adds the SAL value to the COMM value (substituting the value 0 for null) and determines whether the sum is greater than the number constant 2500:
NVL(sal, 0) + NVL(comm, 0) > 2500
Logical operators can combine multiple conditions into a single condition. For example, you can use the AND operator to combine two conditions:
(1 = 1) AND (5 < 7)
For more information on how to evaluate conditions with logical operators, see the section "Logical beginning" .
Examples
ename = 'SMITH'
emp.deptno = dept.deptno
hiredate > '01-JAN-88'
job IN ('PRESIDENT', 'CLERK', 'ANALYST')
sal BETWEEN 500 AND 1000
comm IS NULL AND sal = 2000
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |