Functions in oracle sql

Functions
 Functions are a very useful and powerful part of the SQL language. They can
transform data in a way that is different from the way it is stored in a database. A function is atype of
formula whose result is one of two things: either a transformation, such as a change in the name of a
student to uppercase letters, or information, such as the lengthof a word in a column. Most functions
share similar characteristics, including a name, and typically at least one input parameter, also called
an argument, inside a pair ofmatching parentheses.
function_name(input_parameter)
Data Types
Each value in Oracle has a data type associated with it. A data type determines the value’s attributes
and acceptable values. For example, you cannot enter a text value into aNUMBER data type column
or enter an invalid date, such as 32-DEC-2008, into a DATE data type column. In most SQL
operations, you use the NUMBER, VARCHAR2, andDATE data types. These are the commonly
used data types where the vast majority of data is stored. This chapter concentrates on functions
related to character andnumeric data
The DUAL Table
DUAL is a table unique to Oracle. It contains a single row and a single column called DUMMY and
holds no significant data of its own. It can be used in conjunction with functions to select values that
donot exist in tables, such as text literals or today’s date.A single row is always returned in the result
set. Some of the subsequent SQL examples are not concerned with specific rows but instead use
literals to demonstrate the purpose of a function. You might wonder why the DUAL table does not
appear in the list of tables below Student Connection. This table does not belong to the STUDENT
schema. It belongs to the user SYS, which is the owner of all Oracle system tables. This table, like
many other Oracle system tables, is accessible to all the users via a public synonym
Character function—
All character functions require alphanumeric input parameters. The input can be a text literal or
character literal, sometimes referred to as a string, or text, constant, or a column of data type
VARCHAR2, CHAR, or CLOB. Text literals are always surrounded by single quotation marks. This
lab discusses the most frequently used character functions
Common Oracle Text Functions

The LOWER Function
The LOWER function transforms data into lowercase. The following query shows how both a
column and a text constant serve as individual parameters for the LOWER function.
SQL> SELECT
2 'ORACLE' String,
3 LOWER('ORACLE') Lower
4 FROM DUAL;
STRING LOWER
SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate 2*
FROM Emp:
SQL> SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3FROM Emp
Sql> SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3FROM Emp
4* WHERE LOWER(Job) = 'manager';
The UPPER and INITCAP Functions

The UPPER function is the exact opposite of the LOWER function: It transforms data into
uppercase. The INITCAP function capitalizes the first letter of a word and lowercases the rest of the
word.
SQL> SELECT
2 'oracle' String,
3 UPPER('oracle') Upper
4 FROM DUAL;
STRING UPPER
oracle ORACLE
SQL> SELECT Ename, UPPER(Ename) Upper
2 FROM Emp;
SQL> SELECT
2 Ename||' is Designated As '||LOWER(Job) 3
FROM Emp
4 WHERE Job = 'MANAGER';
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' is Designated As '||LOWER(Job) Ename 3
FROM Emp
4* WHERE Job = UPPER('manager')
SQL> SELECT
2 'oracle corporation' String,
3 INITCAP('oracle corporation') InitCap 4
FROM DUAl;
STRING INITCAP
oracle corporation Oracle Corporation
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'sample text for demonstration purpose' String,
3 INITCAP('sample text for demonstration purpose') InitCap 4*
FROM DUAL
SQL> /
STRING INITCAP
sample text for demonstration purpose Sample Text For Demonstration Purpose
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'sample1text@for4demonstration%purpose' String,
3 INITCAP('sample1text@for4demonstration%purpose') InitCap
4* FROM DUAL
SQL> /
STRING INITCAP
sample1text@for4demonstration%purpose Sample1text@For4demonstration%Purpose
SQL> SELECT
2 Ename, INITCAP(Ename) InitCap
3 FROM Emp;
SQL> SELECT

2 Ename||' is Designated As '||INITCAP(Job) Employee 3
FROM Emp
4 WHERE LOWER(Job) = 'manager';
Concatenation
Concatenation connects strings together to become one. Strings can be concatenated to produce a
single column in the result set. There are two methods of concatenation inOracle: One is with the
CONCAT function, the other is the concatenation operator (||), which is two vertical bars or pipe
symbols. The syntax of the CONCAT function is as follows.
CONCAT(char1, char2)
SQL> SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', 'Corporation') Concat 5
FROM DUAL;
STRIMG STRING2 CONCAT
Oracle Corporation OracleCorporation
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', CONCAT(' ', 'Corporation')) Concat 5*
FROM DUAL
SQL> /
STRIMG STRING2 CONCAT
Oracle Corporation Oracle Corporation
SQL> SELECT
2 CONCAT(CONCAT(INITCAP(Ename),' is a '), Job) Job
3 FROM Emp
4 WHERE Deptno IN(10, 20);
JOB
King is a PRESIDENT
Clark is a MANAGER
Jones is a MANAGER
Ford is a ANALYST
Smith is a CLERK
Scott is a ANALYST
Adams is a CLERK
Miller is a CLERK
8 rows selected.
The SUBSTR Function
SUBSTR transforms a string, returning a substring or subset of a string, based on its input
parameters.
The SUBSTR function’s first input parameter is a string; the second is the starting position of the
subset; the third is optional, indicating the length of the subset. If thethird parameter is not used, the
default is to display the remainder of the string. Here is the syntax for SUBSTR.
SUBSTR(char1, starting_position [, substring_length])

If starting_position is a negative number, Oracle starts counting from the end of the string;
SQL> SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 0, 5) SubStr
4 FROM DUAL;
STRING SUBST
ABCDEFGHIJ ABCDE
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBSTR
ABCDEFGHIJ DEFGHIJ
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4, 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ DEFG
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', -5, 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ FGHI
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5.25, 4.65) SubStr 4*
FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ EFGH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5, -4) SubStr
4* FROM DUAL
SQL> /
STRING S
ABCDEFGHIJ

SQL> SELECT Ename, Job, Deptno
2 FROM Emp
3 WHERE
4 SUBSTR(Job, 4, 3) = 'AGE';
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, SUBSTR(Job, 4, 3) SubStr, Deptno 2*
FROM Emp
SQL> /
SQL> SELECT
2 CONCAT(
3 CONCAT(
4 CONCAT(
5 CONCAT(INITCAP(Ename), '''s Designation is '), INITCAP(Job)),
' But He is a '),
6 CONCAT(
7 INITCAP( SUBSTR(Job, 1, 3)),' Eater.'))
8 FROM Emp
9 WHERE SUBSTR(Job, 4, 3) = UPPER('Age');
The LENGTH Function
The LENGTH function determines the length of a string, expressed as a number. The following SQL
statement selects a text literal from the DUAL table in conjunction with the LENGTH function.
SELECT LENGTH('Hello there')
FROM dual
LENGTH('HELLOTHERE')
--------------------
11
1 row selected
SQL> SELECT
2 'ORACLE' String,
3 LENGTH('ORACLE') Length
4 FROM DUAL;
STRING LENGTH
ORACLE 6
SQL> SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength
SQL> /
Enter value for glength: 4
ENAMELENGTH
KING Your Name is A Collection of 4 Characters.

WARD Your Name is A Collection of 4 Characters.
FORD Your Name is A Collection of 4 Characters.
SQL>
1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength AND Ename LIKE '%A%'
SQL> SELECT
2 INITCAP(Ename) Name,
3 Job
4 FROM Emp
5 WHERE LENGTH(Job) = 7;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 INITCAP(Ename),
3 Job
4 FROM Emp
5* WHERE SUBSTR(Job, 4, LENGTH(SUBSTR(Job, 4, 3))) = 'AGE'
SQL> /
INITCAP(EN JOB
Blake MANAGER
Clark MANAGER
Jones MANAGER
SQL> cl scr
The INSTR Function
INSTR, meaning in string, looks for the occurrence of a string inside another string, returning the
starting position of the search string within the target string. Unlike the other string functions,
INSTR does not return another string; rather, it returns a number
INSTR(char1, char2 [,starting_position [, occurrence]])
INSTR can take two optional input parameters. The third parameter allows you to specify the start
position for the search. The fourth parameter specifies which occurrence of the string to look for.
When these optional parameters are not used, the default value is 1
SQL> SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'R') InString
4 FROM DUAL;
ORIGIN INSTRING
STRING 3
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RIN', 2) InString 4*
FROM DUAL
SQL> /
ORIGIN INSTRING

STRING 3
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RN', 2) InString
4* FROM DUAL
SQL> /
ORIGIN INSTRING
STRING 0
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 2) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 14
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 1) InString
4 FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 1) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 2
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 2) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 5, 3) InString
4FROM DUAL;
ORIGINAL INSTRING

CORPORATE FLOOR 0
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', -7, 1) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> SELECT
2 Ename, Job, INSTR(Job, 'A', 1, 2) InString 3
FROM Emp
4 WHERE Job = 'MANAGER';
ENAME JOB INSTRING
BLAKE MANAGER 4
CLARK MANAGER 4
JONES MANAGER 4
SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString 5
FROM Emp
6 WHERE Job = 'MANAGER';
ENAME ORIGINAL SUB
BLAKE MANAGER AGE
CLARK MANAGER AGE
JONES MANAGER AGE
SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString 5
FROM Emp
6 WHERE SUBSTR(Job, INSTR(Job, 'A', 1, 2), 1) = 'A';
SQL> SELECT
2 'Sample_Text' String,
3 'String',
4 'Text'
5 FROM DUAL;
STRING 'STRIN 'TEX
Sample_Text String Text
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Sample_Text' String,
3 SUBSTR('Sample_Text', 1, 6) Sample, 4
SUBSTR('Sample_Text', 8) Text
5* FROM DUAL
SQL> /
STRING SAMPLE TEXT
Sample_Text Sample Text
SQL> SELECT

2 'Sample_Text' OrgString,
3 SUBSTR('Sample_Text', 1, INSTR('Sample_Text', '_', 1, 1) - 1) Sample, 4
SUBSTR('Sample_Text', INSTR('Sample_Text', '_', 1, 1) + 1) Text
5 FROM DUAL;
ORGSTRING SAMPLE TEXT
Sample_Text Sample Text
The LPAD and RPAD Functions
The LPAD and RPAD functions also transform data: They left pad and right pad strings,
respectively. When you pad a string, you add to it. These functions can add characters, symbols, or
even spaces to your result set. Unlike the LOWER, UPPER, or INITCAP functions, these functions
take more than one parameter as their input
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])
SQL> SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15, '*.') LPadded
4 FROM DUAL;
MYSTRI LPADDED
Page 1 *.*.*.*.*Page 1
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15) LPadded
4* FROM DUAL
SQL> /
MYSTRI LPADDED
Page 1 Page 1
SQL> SELECT
2 Ename,
3 LPAD(Ename, 10, '-') LPad
4 FROM Emp WHERE Sal >= 2500;
ENAME LPAD
KING ------KING
BLAKE -----BLAKE
JONES -----JONES
FORD ------FORD
SCOTT -----SCOTT
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename,
3 LPAD(Ename, 10) LPad
4* FROM Emp WHERE Sal >= 2500
SQL> /
ENAME LPAD
KING KING
BLAKE BLAKE

JONES JONES
FORD FORD
SCOTT SCOTT
SQL> SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15, '*.') RPad
4 FROM DUAL;
STRING RPAD
Page 1 Page 1*.*.*.*.*
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15) RPad
4* FROM DUAL
SQL> /
STRING RPAD
Page 1 Page 1
SQL> SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10, '-'), 15, '-') Center 4
FROM Emp;
ENAME CENTER
KING -----KING------
BLAKE -----BLAKE-----
CLARK -----CLARK-----
JONES -----JONES-----
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10), 15) Center
4* FROM Emp
The LTRIM, RTRIM, and TRIM Functions
LTRIM and RTRIM are the opposite of LPAD and RPAD because they trim, or remove, unwanted
characters, symbols, or spaces in strings. In this example, you see the use of the DUAL table to trim
the zero (0) from the left, the right, and both sides. If both the left and right sides of the string are
trimmed, you need to nest the function. The result of one function provides the input for the other
function.
LTRIM(char1 [, char2])
RTRIM(char1 [, char2])
The TRIM function removes leading characters, trailing characters, or both, effectively doing the job
of LTRIM and RTRIM in one function. If you want the function to act like LTRIM, specify
LEADING as the first parameter; for RTRIM, use the TRAILING option; for both, either specify the
BOTH keyword or omit it altogether
TRIM([LEADING|TRAILING|BOTH] char1 FROM char2)

SQL> SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xy') Ltrim 4
FROM DUAL;
ORGSTR LTRIM
xyzXxyLAST WORD zXxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzy') Ltrim 4*
FROM DUAL
SQL> /
ORGSTR LTRIM
xyzXxyLAST WORD XxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzyX') Ltrim 4*
FROM DUAL
SQL> /
ORGSTR LTRIM
xyzXxyLAST WORD LAST WORD
sql> SELECT
2 ' LAST WORD' OrgStr,
3 LTRIM(' LAST WORD') Ltrim
4* FROM DUAL
SQL> /
ORGSTR LTRIM
LAST WORD LAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xy') Rtrim
4* FROM DUAL
SQL> /
STRING RTRIM
BROWNINGyxXxy BROWNINGyxX
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xyX') Rtrim 4*
FROM DUAL
SQL> /
STRING RTRIM
BROWNINGyxXxy BROWNING
SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'BROWNING ' String,
3 RTRIM('BROWNING ') Rtrim
4* FROM DUAL
SQL> /
STRING RTRIM
BROWNING BROWNING
SQL> cl scr
SQL> SELECT
2 Ename, Job, Sal
3 FROM Emp
4 WHERE
5 LTRIM(Job, 'MAN') LIKE 'GER';
ENAME JOB SAL
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
SQL> cl scr
SQL> SELECT
2 'MITHSS' String,
3 TRIM('S' FROM 'MITHSS') Trimmed
4 FROM DUAL;
STRING TRIM
MITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITH' String,
3 TRIM('S' FROM 'SSMITH') Trimmed
4* FROM DUAL
SQL> /
STRING TRIM
SSMITH MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM('S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /
STRING TRIM
SSMITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(LEADING 'S' FROM 'SSMITHSS') Trimmed 4*

FROM DUAL
SQL> /
STRING TRIMME
SSMITHSS MITHSS
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(TRAILING 'S' FROM 'SSMITHSS') Trimmed 4*
FROM DUAL
SQL> /
STRING TRIMME
SSMITHSS SSMITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'S' FROM 'SSMITHSS') Trimmed 4*
FROM DUAL
SQL> /
STRING TRIM
SSMITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed 4*
FROM DUAL
SQL> /
TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed
*
ERROR at line 3:
ORA-30001: trim set should have only one character
The REPLACE Function
The REPLACE function replaces one string with another string. In the following example, when the
string ‘hand’ is found within the string ‘My hand is asleep’, it is replaced by the string ‘foot’.
REPLACE(char, if, then)
The second parameter looks to see if a string exists within the first parameter. If so, it displays the
third parameter. If the second parameter is not found, then the original string is displayed
SQL> SELECT
2 'JACK AND JUE' OrgStr,
3 REPLACE('JACK AND JUE' , 'J', 'BL') Replace 4
FROM DUAL;
ORGSTR REPLACE
JACK AND JUE BLACK AND BLUE

SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'JACK AND JUE' OrgStr,
3 REPLACE('JACK AND JUE' , 'J') Replace 4*
FROM DUAL
SQL> /
ORGSTR REPLACE
JACK AND JUE ACK AND UE
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'JACK AND JUE' OrgStr,
3 LENGTH('JACK AND JUE') - LENGTH(REPLACE('JACK AND JUE' , 'J')) "N J's
Found"
4* FROM DUAL
SQL> /
SQL> /
ORGSTR N J's Found
JACK AND JUE 2
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 REPLACE(JOB, 'MAN', 'DAM') Replace
3 FROM Emp
4* WHERE Job = 'MANAGER'
SQL> /
ENAME REPLACE
BLAKE DAMAGER
CLARK DAMAGER
JONES DAMAGER
SQL> SELECT Ename, Job,
2 REPLACE (Job, 'P') Replace
3 FROM Emp
4 WHERE Job = 'PRESIDENT';
ENAME JOB REPLACE
KING PRESIDENT RESIDENT
SQL> SELECT Job,
2 REPLACE (Job, 'MAN', 'EXECUTIVE') Replace 3
FROM Emp
4 WHERE Job = 'SALESMAN';
The TRANSLATE Function
Unlike REPLACE, which replaces an entire string, the TRANSLATE function provides a one-forone
character substitution
TRANSLATE(char, if, then)
SQL> SELECT Ename, Job,
2 TRANSLATE(Job, 'P', ' ') Translate
3 FROM Emp
4 WHERE Job = 'PRESIDENT';
ENAME JOB TRANSLATE
KING PRESIDENT RESIDENT
SQL> ED

Wrote file afiedt.buf
1 SELECT Ename, Job,
2 TRANSLATE(Job, 'P') Translate
3 FROM Emp
4* WHERE Job = 'PRESIDENT'
SQL> /
TRANSLATE(Job, 'P') Translate
*
ERROR at line 2:
ORA-00909: invalid number of arguments
SQL> SELECT Job,
2 TRANSLATE(Job, 'MN', 'DM') Translate 3
FROM Emp
4 WHERE Job = 'MANAGER';
JOB TRANSLATE
MANAGER DAMAGER
MANAGER DAMAGER
MANAGER DAMAGER
The SOUNDEX Function
The SOUNDEX function allows you to compare differently spelled words that phonetically sound
alike. The next query uses the SOUNDEX function to display students whose last name sounds like
Martin.
SELECT student_id, last_name
FROM student
WHERE SOUNDEX(last_name) = SOUNDEX('MARTIN')
STUDENT_ID LAST_NAME
Using the ASCII and CHR Functions
SQL> select ascii('a'), ascii('z')
2 , ascii('A'), ascii('Z')
3 , ascii('ABC'), chr(77)
4 from dual;
ASCII('A') ASCII('Z') ASCII('A') ASCII('Z') ASCII('ABC') CHR(77)
---------- ---------- ---------- ---------- ------------ -------
97 122 65 90 65 M
If you compare the third and the fifth columns of the result, you can see that the ASCII function
considers only the first character of its argument, regardless of the length of the input text
SQL> select ascii('a'), ascii('z')
2 , ascii('A'), ascii('Z')
3 , ascii('ABC'), chr(77)
4 from dual;
ASCII('A') ASCII('Z') ASCII('A') ASCII('Z') ASCII('ABC') CHR(77)
---------- ---------- ---------- ---------- ------------ -------
97 122 65 90 65 M

Arithmetic Functions-----
The SIGN Function
The SIGN function tells you the sign of a value, returning a number 1 for a positive number, -1 for a
negative number, or 0 for zero. The following example compares SIGN with the ABS function.
SELECT -14, SIGN(-14), SIGN(14), SIGN(0), ABS(-14)
FROM dual
-14 SIGN(-14) SIGN(14) SIGN(0) ABS(-14)
-14 -1 1 0 14
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3* WHERE SIGN(Sal - Comm) = -1
SQL> /
ENAME SAL COMM
MARTIN 1250 1400
SQL> SELECT 'The Current calender in Server is : '||SYSDATE Today FROM DUAL;

ROUND and TRUNC Functions
ROUND and TRUNC are two useful functions that round and truncate (or cut off) values,
respectively, based on a given number of digits of precision. The next SELECT statement illustrates
the use of ROUND and TRUNC, which both take two input parameters. Observe the differences in
the result
>SELECT 222.34501,
ROUND(222.34501, 2),
TRUNC(222.34501, 2)
FROM dual;
222.34501 ROUND(222.34501,2) TRUNC(222.34501,2)
222.34501 222.35 222.34
1 row selected.
Here, ROUND (222.34501, 2) rounds the number 222.34501 to two digits to the right of the
decimal, rounding the result up to 222.35, following the normal convention for rounding. In contrast,
TRUNC cuts off all digits beyond two digits to the right of the decimal, resulting in
222.34. ROUND and TRUNC can be used to affect the left side of the decimal as well by passing a
negative number as a parameter.
>SELECT 222.34501,
ROUND(222.34501, -2),
TRUNC(222.34501, -2)
FROM dual
222.34501 ROUND(222.34501,-2) TRUNC(222.34501,-2)
222.34501 200 200
The following is the syntax for ROUND and TRUNC.
ROUND(value [, precision])
TRUNC(value [, precision])
Numbers with decimal places may be rounded to whole numbers by omitting the second parameter,
or specifying a precision of 0.
>SELECT 2.617, ROUND(2.617), TRUNC(2.617)
FROM dual
2.617 ROUND(2.617) TRUNC(2.617)
2.617 3 2
1 row selected.
You can use the TRUNC and ROUND functions not only on values of the NUMBER data type but
also on the DATE data type,
FLOOR and CEIL Functions

The CEIL function returns the smallest integer greater than or equal to a value; the FLOOR function
returns the largest integer equal to or less than a value. These functions perform much like the
ROUND and TRUNC functions, without the optional precision parameter.
>SELECT FLOOR(22.5), CEIL(22.5), TRUNC(22.5), ROUND(22.5)
FROM dual
FLOOR(22.5) CEIL(22.5) TRUNC(22.5) ROUND(22.5)
22 23 22 23
1 row selected.
The syntax for the FLOOR and CEIL functions is as follows.
FLOOR(value)
CEIL(value)
The MOD Function
MOD is a function that returns the modulus, or the remainder of a value divided by another value. It
takes two input parameters, as in the following SELECT statement.
>SELECT MOD(23, 8)
FROM dual
MOD(23,8)
7
1 row selected.
The MOD function divides 23 by 8 and returns a remainder of 7. The following is the syntax for
MOD.
MOD(value, divisor)
The MOD function is particularly useful if you want to determine whether a value is odd or even. If
you divide by 2 and the remainder is a zero, this indicates that the value is even; if the remainder is
1, it means that the value is odd.
The REMAINDER Function
The REMAINDER function calculates the remainder, according to the IEEE specification. The
syntax is as follows.
REMAINDER(value, divisor)
The difference between REMAINDER and the MOD function is that MOD uses FLOOR in its
computations, whereas REMAINDER uses ROUND. The next example shows that the results
between the MOD and REMAINDER functions can be different.
>SELECT MOD(23,8), REMAINDER(23,8)

FROM DUAL
MOD(23,8) REMAINDER(23,8)
7 -1
1 row selected.
Date Functions
let’s first review the syntax to specifydate/time-related constants (or literals), using predefined
ANSI/ISO SQL standard formats.
SQL> select * from nls_session_parameters;
If you execute this query, you will see the current values for NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

1 SELECT Ename, SYSDATE Today, HireDate,
2TRUNC(SYSDATE - HireDate) DateDiff
3* FROM Emp
SQL> /
ENAME TODAY HIREDATE DATEDIFF
KING 07-JUL-10 17-NOV-81 10459
BLAKE 07-JUL-10 01-MAY-81 10659
CLARK 07-JUL-10 09-JUN-81 10620
…..
….
Sql> SELECT Ename, SYSDATE Today, HireDate,
2TRUNC(SYSDATE - HireDate) Days,
3 TRUNC((SYSDATE - HireDate) / 365) Years
4FROM Emp
5 WHERE
6* TRUNC((SYSDATE - HireDate) / 365) > 28
SQL> SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added
2 FROM Emp;
SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added
*
ERROR at line 1:
ORA-00975: date + date not allowed
SQL> SELECT
2 '07-JUL-10' Today,
3 '07-JUL-10' + 3 "3 Days"
4 FROM DUAL;
'07-JUL-10' + 3 "3 Days"
*
ERROR at line 3:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE Today,
3 SYSDATE - '01-MAY-10' Diff
4 FROM DUAL;
SYSDATE - '01-MAY-10' Diff
*
ERROR at line 3:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE Today,

3 SYSDATE + 90 "3 Months"
4 FROM DUAL;
TODAY 3 Months
07-JUL-10 05-OCT-10
SQL> select sysdate from dual;
SYSDATE
---------
05-NOV-13
SQL> select sysdate+4 from dual;
SYSDATE+4
---------
09-NOV-13
SQL> select '09-nov-13'+3 from dual;
select '09-nov-13'+3 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, 3) "3 Months"
4 FROM DUAL;
TODAY 3 Months
07-JUL-10 07-OCT-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, -3) "3 Months"
4* FROM DUAL
SQL> /
TODAY 3 Months
07-JUL-10 07-APR-10
SQL> SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 MONTHS_BETWEEN(SYSDATE, HireDate) "Months" 4
FROM Emp;
ENAME SAL TODAY HIREDATE Months
KING 5000 07-JUL-10 17-NOV-81 343.693194
BLAKE 2850 07-JUL-10 01-MAY-81 350.209323
‘’’’’’’
‘’’’’’’’
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
4* FROM Emp

SQL> /
ENAME SAL TODAY HIREDATE Months
KING 5000 07-JUL-10 17-NOV-81 343
BLAKE 2850 07-JUL-10 01-MAY-81 350
CLARK 2450 07-JUL-10 09-JUN-81 348
;;;;;;;;
;;;;;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months",
4 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) "Years"
5FROM Emp
6 WHERE
7* TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28
SQL> /
ENAME SAL TODAY HIREDATE Months Years
BLAKE 2850 07-JUL-10 01-MAY-81 350 29
CLARK 2450 07-JUL-10 09-JUN-81 348 29
JONES 2975 07-JUL-10 02-APR-81 351 29
ALLEN 1600 07-JUL-10 20-FEB-81 352 29
WARD 1250 07-JUL-10 22-FEB-81 352 29
SMITH 800 07-JUL-10 17-DEC-80 354 29
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', '10-FEB-10') Months
5* FROM DUAL
SQL> /
TODAY PAST MONTHS
07-JUL-10 07-FEB-10 4.90322581
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-FEB-10', '07-JUL-10') Months
5* FROM DUAL
SQL> /
TODAY PAST MONTHS
07-JUL-10 07-FEB-10 -5
SQL> SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRI') "Friday"
4 FROM DUAL;
TODAY Friday
07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 6) "Friday"
4* FROM DUAL
SQL> /
TODAY Friday
07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRIDAY') "Friday"
4*FROM DUAL
SQL> /
TODAY Friday
07-JUL-10 09-JUL-10
SQL> SELECT
2 SYSDATE Today,
3 LAST_DAY(SYSDATE) LastDay
4 FROM DUAL;
TODAY LASTDAY
07-JUL-10 31-JUL-10
SQL> SELECT
2 'The Days Left in The Current Month Are : '||
3 (
4 LAST_DAY(SYSDATE) - SYSDATE
5 ) "Days Left"
6 FROM DUAL;
Days Left
The Days Left in The Current Month Are : 24
EXTRACT
You can extract various components of a date or timestamp expression with the ANSI/ISO
standard EXTRACT function. Depending on the datatype of the argument d (DATE,
TIMESTAMP,or INTERVAL) the following values for c are supported: YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND,TIMEZONE_ABBR, and so on
SQL> select bdate
2 , extract(year from hiredate) as year_of_join
3 , extract(month from hiredate) as month_of_join
4 , extract(day from hiredate) as day_of_join
5 from emp
6 where ename = 'KING';
BDATE YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
----------- ------------- -------------- ------------

17-NOV-1952 1952 11 17
ROUND and TRUNC

The default format is 'DD', resulting in rounding or truncating to midnight. For example,
TRUNC(SYSDATE) truncates the current system date and time to midnight.
SQL> SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE) Round,
4 TRUNC(SYSDATE) Trunc
5 FROM DUAL;
TODAY ROUND TRUNC
07-JUL-10 08-JUL-10 07-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL
SQL> /
TODAY ROUND TRUNC
07-JUL-10 11-JUL-10 04-JUL-10
SQL> SELECT /* AFIER MAKING SYSTEM DATE 11.40 AM */
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL
TODAY ROUND TRUNC
07-JUL-10 04-JUL-10 04-JUL-10

SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'MONTH') Round,
4 TRUNC(SYSDATE, 'MONTH') Trunc
5* FROM DUAL
SQL> /
TODAY ROUND TRUNC
07-JUL-10 01-JUL-10 01-JUL-10
/* CHNGE SYSTEM DATE TO 16-JUL-10 */
SQL> /
TODAY ROUND TRUNC
16-JUL-10 01-AUG-10 01-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'YEAR') Round,
4 TRUNC(SYSDATE, 'YEAR') Trunc
5* FROM DUAL
TODAY ROUND TRUNC
07-JUL-10 01-JAN-11 01-JAN-10

SQL> SELECT Ename, Sal, Comm, Sal - Comm Diff
2 FROM Emp
3 WHERE Comm IS NOT NULL;
ENAME SAL COMM DIFF
MARTIN 1250 1400 -150
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999MI') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 150-
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999PR') Diff
3 FROM Emp

4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 <150>
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999PT') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 (150)
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '|| 3
TO_CHAR(&GVal2, 'RN') "Deci 2 Roman"
4 FROM DUAL;
Enter value for gval1: 1
Enter value for gval2: 1
Deci 2 Roman
The Roman Number of Decimal 1 is I
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '|| 3
LTRIM(TO_CHAR(&GVal2, 'RN')) "Deci 2 Roman"
4* FROM DUAL
SQL> /
Enter value for gval1: 8
Enter value for gval2: 8
Deci 2 Roman
The Roman Number of Decimal 8 is VIII
SQL> SELECT
2 Ename, Sal, Comm, TO_CHAR(Sal - Comm, '9G999D99S') Diff 3
FROM Emp
4 WHERE Comm IS NOT NULL;
ENAME SAL COMM DIFF
MARTIN 1250 1400 150.00-
ALLEN 1600 300 1,300.00+
TURNER 1500 0 1,500.00+
WARD 1250 500 750.00+
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Hexadecimal Value of Decimal '||&GVal1||' is '|| 3
TO_CHAR(&GVal2, 'x') "Deci 2 Hexa"
4* FROM DUAL
SQL> /
Enter value for gval1: 10

Enter value for gval2: 10
Deci 2 Hexa
The Hexadecimal Value of Decimal 10 is a
1 SELECT Ename,
2 TO_CHAR(Sal, '0999') Sal,
3 TO_CHAR(Comm, '0999') Comm
4* FROM Emp
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(Comm, '0G999D99') Comm
4* FROM Emp
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
5*FROM Emp
SQL> /
ENAME SAL COMM TOTSAL
KING 5,000.00 0,000.00 5,000.00
BLAKE 2,850.00 0,000.00 2,850.00
CLARK 2,450.00 0,000.00 2,450.00
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99C') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99C') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99C') TotSal 5*
FROM Emp
Changing the Date Display Format
When you query a DATE data type column, Oracle displays it in the default format determined by
the database NLS_DATE_FORMAT parameter. The most frequent setup values you will see are
DD-MON-YYYY and DD-MON-RR. The RR represents a two-digit year based on the century; if
the two-digit year is between 50 and 99, then it’s the previous century; if the two-digit year is
between 00 and 49, it’s the current century The TO_CHAR conversion function changes the DATE
data type into text and applies a format mask. As you see from the syntax listed in Table 5.3, the
function takes a DATE data type as the first parameter; the second optional parameter is for the
format mask.
Date-Related Conversion Functions



Implicit Conversion and Default Date Format
Without a format mask, Oracle can implicitly perform a conversion of the text literal
to The DATE data type when the text literal is in the default date format. This default
format is determined by the NLS_DATE_FORMAT, an Oracle instance parameter.
The SYSDATE Function
The SYSDATE function returns the computer operating system’s current date and
time and does not take any parameters. If you connect to the database server via a
client machine, it returns the date and time of the machine hosting the database, not
the date and time of your client machine. For example, if your client workstation is
located in New York, your local time zone is Eastern Standard Time (EST); if you
connect to a server in California, you receive the server’s Pacific Standard Time (PST)
date and time. To include the time in the result, you use the TO_CHAR function
together with the appropriate format mask.
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI')
FROM dual
SYSDATE TO_CHAR(SYSDATE,'
26-OCT-08 26-OCT-2008 19:49
1 row selected.
SQL> SELECT
2 'Today is : '||SYSDATE||TO_CHAR(SYSDATE, 'B.C.') Today 3
FROM DUAL;
TODAY
Today is : 09-JUL-10A.D.
SQL> SELECT Ename, Sal, HireDate||TO_CHAR(HireDate, ' B.C.') HireDate

2 FROM Emp;
SQL> SELECT
2 'The Meridian in My Clock is : '|| 3
TO_CHAR(SYSDATE, 'P.M.') Meridian 4 FROM
DUAL;
MERIDIAN
The Meridian in My Clock is : A.M.
SQL> SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'CC, B.C.') Calendar 4
FROM DUAL;
CALENDAR
The Current Date is 09-JUL-10 21, A.D.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'SCC, B.C.') Calendar 4*
FROM DUAL
SQL> /
CALENDAR
The Current Date is 09-JUL-10 21, A.D.
SQL> cl scr
SQL> SELECT
2 Ename,
3 HireDate||' '||TO_CHAR(HireDate, 'CC, B.C.') HireDate 4
FROM Emp;
SQL> SELECT
2 'The Weekday Number of Today : '|| 3
TO_CHAR(SYSDATE, 'D') WeekDay
4 FROM DUAL;
WEEKDAY
The Weekday Number of Today : 6
SQL> SELECT
2 Ename||' You Joined on The Weekday of '|| 3
TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' You Joined on The Weekday of '|| 3
TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp
5* WHERE TO_CHAR(HireDate, 'D') = &GWeekday
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Today is '||SYSDATE||TO_CHAR(SYSDATE, ' Day') Today 3*
FROM DUAL
SQL> /
TODAY

Today is 09-JUL-10 Friday
SQL> SELECT
2 Ename||' You Are Recruited on '||HireDate|| 3
TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' You Are Recruited on '||HireDate|| 3
TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp
5* WHERE RTRIM(TO_CHAR(HireDate, 'Day')) = 'Friday'
SQL> /
HIREDATE
BLAKE You Are Recruited on 01-MAY-81 Friday
ALLEN You Are Recruited on 20-FEB-81 Friday
SQL> SELECT
2 'We Are on the Day '||TO_CHAR(SYSDATE, 'DD')|| 3
' of This Month.' MonthDay
4 FROM DUAL;
MONTHDAY
We Are on the Day 09 of This Month.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current day Running in This Year : '|| 3
TO_CHAR(SYSDATE, 'DDD') Yearday
4* FROM DUAL
SQL> /
YEARDAY
The Current day Running in This Year : 190
SQL> SELECT Ename, Sal, HireDate
2 FROM Emp
3 WHERE
4 TO_CHAR(HireDate, 'DDD') > 245;
ENAME SAL HIREDATE
KING 5000 17-NOV-81
MARTIN 1250 28-SEP-81
;;;
7 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, HireDate
2 FROM Emp
3 WHERE
4* TO_CHAR(HireDate, 'DDD') BETWEEN 245 AND 300
SQL> /
ENAME SAL HIREDATE
MARTIN 1250 28-SEP-81
TURNER 1500 08-SEP-81
SQL> SELECT
2 'Today is : '||TO_CHAr(SYSDATE, 'DY') Today 3
FROM DUAL;

TODAY
Today is : FRI
SQL> cl scr
SQL> SELECT
2 'The Current Week of This Year is : ' 3
||TO_CHAR(SYSDATE, 'IW') YearWeek
4 FROM DUAL;
YEARWEEK
The Current Week of This Year is : 27
SQL> SELECT
2 Ename, Sal, HireDate, TO_CHAR(HireDate, 'IW') YearWeek 3
FROM Emp
4 WHERE TO_CHAR(HireDate, 'IW') > 25;
ENAME SAL HIREDATE YE
KING 5000 17-NOV-81 47
MARTIN 1250 28-SEP-81 40
;;;;
SQL> SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C.') ThisYear 3
FROM DUAL;
THISYEAR
The Current Year is : 2010, A.D.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C. "Century : "CC')
ThisYear
3* FROM DUAL
SQL> /
THISYEAR
The Current Year is : 2010, A.D. Century : 21
SQL> SELECT
2 Ename||'''s Recruited Year is '||TO_CHAR(HireDate, 'IYYY, B.C.') EmpHire 3
FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||'''s Recruited Year is '||
3 TO_CHAR(HireDate, 'YYYY "=>" Year, B.C.') EmpHire 4*
FROM Emp
SQL> /
EMPHIRE
KING's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
BLAKE's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
CLARK's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
;;;;;;;;;;;;;;;;;;;;;
;;;;;
SQL> SELECT
2 'The Current Year is : '||
3 TO_CHAR(SYSDATE, 'YYYY "=>" Year, B.C.') Year 4
FROM DUAl;
YEAR
The Current Year is : 2010 => Twenty Ten, A.D.
SQL> cl scr

SQL> SELECT
2 'The Current Week in This Month is : '|| 3
TO_CHAR(SYSDATE, 'W') MonthWeek
4 FROM DUAl;
MONTHWEEK
The Current Week in This Month is : 2