CONVERSION AND
MISCELLANEOUS FUNCTIONS
Conversion functions
Conversion functions are used to convert a value from
one data type into another. These functions are not
required if Oracle can automatically convert the
value. But there are cases where these conversion
functions are required to convert the value to the
required data type.
Function
Description
TO_CHAR (value [, format])
Converts value, which is of
DATE or NUMBER type, to CHAR
type.
Converts the given CHAR type
value to DATE type.
Converts given CHAR type value
to NUMBER type.
TO_DATE (char [, format])
TO_NUMBER (char)
Automatic Conversion Of Data Type
Any NUMBER or DATE will be converted to a CHAR.
If DATE is a literal enclose it in quotes.
CHAR type will be converted to NUMBER if it
contains only digits, decimal point, or minus sign
on the left.
CHAR will be converted to DATE type if it is in DDMON-YY or DD-MON-YYYY format.
A DATE will NOT be converted to NUMBER.
A NUMBER will NOT be converted to DATE.
Automatic Conversion Of Data Type
Example :
1.
2.
3.
Automatic Conversion Of Data Type
Example :
Oracle returns an error saying the date is not having
valid month because Oracle expects months to be of
first three letters of the month name. As we have
given only month number, it is not acceptable to
Oracle. In this case we need to explicitly convert the
value to DATE type using TO_DATE function.
TO_CHAR functions
This function is used to convert the given DATE or
NUMBER to CHAR type. TO_CHAR function may also be
used to format the given date or number while
converting the value to CHAR type.
Example :
1.
2.
TO_CHAR and TO_DATE formats
Format Option
MM
MON
MONTH
DDD
DD
D
Day
YYYY
YY
YEAR
HH or HH12
HH24
MI
SS
A.M. or P.M.
Fm
TH
SP
Description
Number of the month: 10
First three letters of month name: OCT
Complete month name: OCTOBER
Day of the year since January 1st: 340
Day of the month: 16
Day of the week: 5
Day fully spelled: Wednesday
Four digits year: 1996
Two digits year: 96
Year spelled out: NINTEEN-NINTY-SIX
Hour of the day: 5
Hour of the day: 20
Minute of hour: 30
Second of minute: 30
Displays A.M. or P.M. depending on the time.
Removes trailing spaces. May becomes May
Suffix to number: DDTH will produce 16th
Number Spelled out: DDSP will produce THIRD
for day 3.
TO_CHAR functions
Example :
1.
2.
TO_CHAR functions
Example :
TO_DATE functions
TO_DATE is used to convert a CHAR type value to
DATE type. If the value is in DD-MON-YY or DD-MMYYYY format then TO_DATE is not needed because
Oracle implicitly converts the value to DATE type.
Example :
TO_NUMBER functions
This function is required in only two occasions.
The following are the two cases.
To convert formatted number to number.
To sort CHAR data in numeric order.
Example :
TO_NUMBER functions
This function is required in only two occasions.
The following are the two cases.
To convert formatted number to number.
To sort CHAR data in numeric order.
Example :
TO_NUMBER functions
Example : Column VNO is defined as VARCHAR2(10)
Miscellaneous Functions
Miscellaneous functions are the functions that can be
used with any data type.
Function
Description
DECODE(expression,cond,value,
cond,value,...,elsevalue)
If expression is equivalent to
first cond then first value is
returned otherwise Oracle
checks whether the expression
is equivalent to second cond
then second value is returned.
If expression doesnt match
with any of the values then
elsevalue is returned.
Returns the greatest of the
given values.
Returns the least value of the
given values.
Return value2 if value1 is null
otherwise returns value1.
GREATEST(value1,value2,...)
LEAST( value1, value2, ...)
NVL(value1,value2)
DECODE functions
This function works like a multiple IF statement or a
CASE/SWITCH statement in a typical programming
language.
It takes a value and compares it with the given
values one by one. Wherever the value is equivalent
to the given value it returns the corresponding
value.
DECODE functions
Example : shows how to decode the GRADE of
COURSE_FACULTY table
DECODE functions
Example : display the total remuneration paid to
faculty. Assuming the payment is based on the time of
the batch and no. of days of the batch.
GREATEST and LEAST functions
These functions take a collection of values and
return a single value which is either the least or
greatest of the given values as the case may be.
GREATEST is used to return the largest of the given
values and LEAST the smallest of the given values.
Example : to given discount of 10% on the course fee
or 500 whichever is higher
GREATEST and LEAST functions
Example : Discount is either 10% of the course fee or
500 whichever is lower
GREATEST and LEAST functions
GREATEST and LEAST will not treat string literal that
is in date format as date. Instead these dates are
taken as strings.
Example :
NVL function
It is used to return the second value if first value
is null. This function has a lot of significance
since Oracle returns a null value from any expression
containing a null value.
Any expression involving a null value will result in
a null value.
NVL function
Example :
But we get nothing actually null value - for NODAYS
of batches b6 and b7 as they are have null value in
ENDDATE. Since Oracle results in null value for any
expression having a null value the result of
ENDDATESTDATE is a null value.
NVL function
Example :
However, now we want to take ending date if batch is
completed otherwise we want to take system date as
ending date.
NVL function
Example :
Now we want to include even the status of the batch,
which will be COMPLETED if ENDDATE is not null
otherwise RUNNING.