Oracle includes three conversion functions: TO_NUMBER, TO_CHAR and TO_DATE.
TO_NUMBER function
Syntax:
Ex.
Asking_price Sale_price
Oracle can execute the following query,
SQL > SELECT Asking_price-Sale_Price AS “Price Difference” FROM Sale_Records;
In this example, Oracle is able to implicitly convert the varchar2 value into numeric value and perform the arithmetic expression. However, if the data in these columns look like this:
Asking_price Sale_price
The previous query return a error:
SQL > SELECT Asking_price-Sale_Price AS “Price Difference” FROM Sale_Records;
ERROR at line 1:
To avoid this error you can use the TO_NUMBER function.
SQL > SELECT TO_NUMBER(Asking_price,’99,999.99’)-TO_NUMBER(Sale_Price,’99,999.99’) AS “Price Difference” FROM Sale_Records;
Note: The last example in this section does not work properly when the column has a fixed-length character data type. This can be dealt with by using the TRIM function inside the TO_NUMBER function.
Ex.
SQL > SELECT TO_NUMBER(TRIM(Asking_price),’99,999.99’)-TO_NUMBER(TRIM(Sale_Price),’99,999.99’) AS “Price Difference” FROM Sale_Records;
TO_CHAR function
Syntax:
TO_CHAR with NUMERIC data
EX.
SQL > SELECT InstructorID, TO_CHAR(PerDiemCost*5,’L99,999.99’) AS “Weekly Cost” FROM Instructors;
Note: When you use “L”, it display the currency symbol specified in the NLS_Currency parameter. In this case the NLS_Currency value is the Dollar sign. If you change the NLS_Currency value to Italy, you receive a different output from the same query.
Ex.
ð In this case, the NLS_Currency value has changed from the dollar sign to the Lira symbol (L.).
To know what your currency NLS_Currency value is, give the query as,
| Symbol | Significance |
| 0 | Display a leading zero. |
| 9 | Represent any number. |
| , | Thousand Separators. |
| . | Floating currency symbol. |
| L | Floating currency symbol. |
| $ | Floating dollar sign. |
| MI | Places minus sign to right of value if the value is nagative. |
Ex.
SQL > SELECT FirstName, LastName, TO_CHAR(PerDiemCost*5,’L99,999.00’) AS “Weekly Cost” FROM Instructors;
Ex.
SQL > SELECT InstructorID, TO_CHAR(PerDiemCost*5,’$999.99’) AS “Weekly Cost” FROM Instructors;
Top |^|
| Symbol | Significance |
| DD | Two digits day value. |
| MM | Two digits month value. |
| Mon | Abbreviated month value. |
| Month | The full month spelled output. |
| YYYY | Four digits year value. |
| year | The full year spelled out. |
| Day | The day of the week. |
| Dy | Three letter abbreviation for the day of the week. |
| Q | The quarter. |
Ex.
51 SATURDAY JANUARY 13,2004
Ex.
Ex.
Ex.
CLASSID Start Date
TO_DATE function
Syntax:
SQL > SELECT ClassID FROM ScheduledClasses WHERE StartDate=TO_DATE(‘January 6,2004’,’Month DD,YYYY’);
50
CHARACTER function
Character functions are used to manipulate case and appearance of character data.
| Function | Description |
| LOWER | Return all alphabetic character in lower cases. |
| UPPER | Return all alphabetic character in upper cases. |
| INITCAP | Capitalizes the first letter of each word and sets the remaining character in lowercase. |
Ex.
UPPER LOWER INITCAP
Ex.
no rows selected.
ð There are instructors in this table from Toronto; however, in the table, the string is stored in initial capital letters. To solve the problem we can use the following query,
SQL > SELECT InstructorId, City FROM Instructors WHERE UPPER(City)=’TORONTO’;
INSTRUCTORID CITY
Character-Manipulation function
| Function | Description |
| SUBSTR | Return a portion of a string. |
| CONCAT | Combines two string values. |
| LPAD | Pads a string with a defined character to a defined length. |
| TRIM | Trims leading or ending character. |
| LENGTH | Return the length of a string value. |
| INSTR | Return the position of a particular character within a string. |
Syntax:
Ex.
Ex.
SUBSTRING
C SQL
nced SQL
Syntax:
Ex.
SQL > SELECT CONCAT(‘Oracle’,’Server’) AS CONCAT FROM Dual;
OracleServer
Syntax: LPAD(char, size of string,’ padding character’)
Ex.
SQL> SELECT LPAD(LastName, 10 ,’*’) AS LPAD FROM Students;
LPAD
*****Jones
*Patterson
*******Hee
TRIM
Syntax:
Ex.
TEVEN
ð if you want only one trimmed, you must specify which string you want trimmed.
Ex.
SQL> SELECT TRIM(TRAILING ‘S’ FROM ‘STEVENS’) AS TRIM FROM Dual;
STEVEN
Ex.
SQL> SELECT TRIM(BOTH ‘S’ FROM ‘Stevens’) AS TRIM FROM Dual;
TRIM
Tevens
ð The character you choose to trim must match the case of the characters in the string.
Ex.
SQL> SELECT TRIM(‘ST’ FROM ‘STEVENS’) AS TRIM FROM Dual;
ERROR at line 1:
ð you can’t include multiple characters in the list of characters to be trimmed.
Ex.
SQL> SELECT FirstName, LENGTH(FirstNAme) AS LENGTH FROM Instructors;
FIRSTNAME LENGTH
Michael 7
Susan 5
David 5
Syntax:
Ex.
SQL> SELECT FirstName, INSTR(FirstNAme, ‘a’) AS INSTR FROM Instructors;
FIRSTNAME INSTR
Michael 5
Susan 4
David 2
Geoff 0
Lana 2
Number function
Number functions enable you to manipulate numeric values.
ROUND
The ROUND function is used to round numeric values to a decimal place specified as a parameter of the function.
Syntax:
Ex.
SQL> SELECT ROUND(44.647 , 2) AS POSITIVE, ROUND(44.647 , 0) AS ZERO, ROUND(44.647 , -1) AS NEGATIVE FROM Dual;
POSITIVE ZERO NEGATIVE
44.65 45 4
ð The value for negative has been rounded to the nearest ten.
TRUNC
Syntax: TRUNC(number, n)
Ex.
SQL> SELECT TRUNC(44.647 , 2) AS POSITIVE, TRUNC (44.647 , 0) AS ZERO, TRUNC (44.647 , -1) AS NEGATIVE FROM Dual;
POSITIVE ZERO NEGATIVE
44.64 44 40
ð It simply truncates the value at the specified decimal place.
MOD
The MOD function returns the remainder of two values.
Syntax:
When this function is called, it divides value1 by value2 and returns only the remainder.
Ex.
SQL> SELECT MOD(2200, 300) AS ODD, MOD(2100,300) AS EVEN FROM Dual;
ODD EVEN
100 0
Ex.
SQL> SELECT MOD(1000, 0) AS ZERO_DIVIDE FROM Dual;
ZERO_DIVIDE
1000
ð The above query should return a mathematical error. However, in Oracle this function returns the Remainder value.
Date function
Oracle includes a number of functions that enable you to modified and manipulate date data. Most of these functions enable you to perform calculations on dates.
| Function | Description |
| ADD_MONTHS | Add or subtract a number of months to date. |
| MONTHS_BETWEEN | Determines the number of months between two dates. |
| NEXT_DAY | Determines the next occurrence of a particular day of the week. |
| LAST_DAY | Determines the last day of the month. |
| ROUND | Round to the nearest date part. |
| TRUNC | Truncates to the nearest date part. |
Syntax:
Ex.
SQL> SELECT StartDate, ADD_MONTHS( StartDate, 6) AS 6_MONTHS FROM ScheduledClasses;
STARTDATE 6_MONTHS
06-JAN-04 06-JUL-04
13-JAN-04 13-JUL-04
14-FEB-04 14-AUG-04
Syntax:
Ex.
SQL> SELECT MONTHS_BETWEEN(‘01-JAN-01’,’01-AUG-01’) AS MONTHS_BETWEEN FROM Duel;
MONTHS_BETWEEN
-7
ð The result of this function returns a negative value because in this example, you are subtracting a higher value from a lower value.
Syntax:
Ex. To find the date of the Friday after each course start date, you use the following query.
SQL> SELECT StartDate, NEXT_DAY(StartDate,’FRIDAY’) AS NEXT_DAY FROM ScheduledClasses;
STARTDATE NEXT_DAY
14-FEB-04 16-AUG-04
Syntax: LAST_DAY(date)
Ex. Suppose you pay your instructions on the last day of each month. You can calculate the payment date with the following query.
SQL> SELECT classID, StartDate, LAST_DAY(StartDate) AS payment FROM ScheduleClasses;
CLASSID STARTDATE PAYMENT
ð Oracle determines the month in which each date value exists and then determines the last day for the month.
ROUND and TRUNC
In the case, you specify the part of the date you want to round or truncate calues on rather than a decimal position. You can round a date to a particular month or year.
Ex.
SQL> SELECT ROUND(StartDate,'MONTH') AS MONTH, ROUND(StartDate,'YEAR') AS YEAR FROM ScheduleCalsses;
MONTH
Top |^|
Use the NUL function
The NVL function is used to provide an actual value in place of a NULL returned by a query.
Syntax: NVL(colume, value)
Ex.
SQL> SELECT InstructorID, (PerDiewCost + PerDiewExpenses)*5 AS "Weekly Cost" FROM Instructors;
INSTRUCTORID Weekly Cost
300 3500
310 3200
210
410
450 3250
ð Any arithmetic expression containing a NULL in one of its values always returns NULL. To solve this problem we can use NUL function.
SQL> SELECT InstructorID, (PerDiewCost + NUL(PerDiewExpenses))*5 AS "Weekly Cost" FROM Instructors;
INSTRUCTORID Weekly Cost
300 3500
310 3200
210 2000
410 2000
450 3250
Note: The value you include in the NUL function must match the data type of the column being evaluated.
SQL> SELECT InstructorID, NUL(PerDiewExpenses,'No expression') FROM Instructors;
ERROR at line 1:
ORA-01722: Invalid number
Using the SYSDATE function
The SYSDATE function is used to return the current date from the system.
Ex.
SQL> SELECT CourseNumber, Startdate FROM ScheduleClasses WHERE Startdate
ð The query always returns courses that started previous to the current day.
Using the DECODE function
The DECODE function enables you to build some condition logic into your queries.
Syntax: DECODE(column/expression, condition1, result1[, condition2, result2][,default])
Ex.
SQL> SELECT InstructorID, PerDiemCost, DECODE(ROUND(PerDiewCost/100,0), 4, 1, 5, 2, 6, 3, 7, 4, 5) AS Rank From Instructors ORDER BY Rank DESC;
INSTRUCTORID PERDIEMCOST RANK
200 750 5
100 600 3
300 500 2
410 400 1
Nesting function
When functions are nested, Oracle evaluate the innermost function first and panes its value to the outer function, only the outer most function actually returns a value back to the result function.
Ex.
SQL> SELECT CONCAT( SUBSTR (LastName, 1,3), SUBSTR( FirstName, 1, 2)) AS CONCAT_ID FROM Instructors;
CONCAT_ID
Har M.
Kee Su
Ung Da
Group/Aggregate Functions
Group (Some times referred to as multi-row) functions differ greatly from the single row functions. They are used to derive aggregate values. All of these functions return one and only one value, regardless of how many values are passed into the function.
| Function | Description |
| AVG | Returns the average of all values inputted |
| SUM | Returns the total of all values inputted |
| COUNT | Counts the number of rows inputted |
| MIN | Returns the lowest of all values inputted |
| MAX | Returns the highest of all values inputted |
A SELECT list can contain one or more functions.
AVG and SUM
The AVG and SUM functions accept only numeric data type values. The AVG function returns the average of all NOT NULL values passed into the function and the SUM function returns the total for all NOT NULL value.
Ex.
SQL> SELECT AVG(PerDiewCost) AS AVERAGE, SUM(PerDiewCost) AS TOTAL FORM Instructors;
AVERAGE TOTAL
506.25 4050
COUNT(Column) and COUNT(*)
The difference between COUNT(Column) and COUNT(*) is that COUNT(*) includes rows where there are NULL values, whereas COUNT(Column) only counts those rows that are not NULL in the specified.
Ex.
SQL> SELECT COUNT(*) AS ALL_ROWS, COUNT(PerDiemExpenses) AS NOT_NULL FROM Instructor;
ALL_ROWS NOT_NULL
8 6
Ex.
SQL> SELECT Max(PerDiewExpenses) AS HIGHEST, MIN(PerDiewExpenses) AS LOWEST FROM Instructors;
HIGHEST LOWEST
250 200
Ex.
SQL> SELECT Max(EnrollmentDate) AS LATEST, MIN(EnrollmentDate) AS EARLIEST FROM Instructors;
LATEST EARLIEST
05-JAN-01 02-DES-00
Note: When you use MIN and MAX with character data, Oracle retrieves the lowest or highest alphabetically i.e. clorest to "a" and elorest to "z".
Ex.
SQL> SELECT Max(LastName) AS "Clorest To A", MIN(LastName) AS "Clorest to Z" FROM Instructors;
Clorest To A Clorest to Z
chiu williams
Ex. Consider a table which bearing data as below.
Employee Hourly_wage
Bob 15
Sue 10
Jane
Rich 5
Now, if we query the table then,
SQL> SELECT AVG(NVL(Hourly_wage,0)) AS AVG_SAL FROM Wages;
AVG_SAL
7.5
Using the DISTINCT keyword with NULL
If you want to consider only unique rows, it is possible to use the DISTINCT operator inside a group function.
Ex.
SQL> SELECT AVG(DISTINCT PerDiewExpenses) AS WITH_DISTINCT, AVG(PerDiewExpenses) AS NO_DISTINCT FROM Instructors;
WITH_DISTINCT NO_DISTINCT
255 208.33333
Ex.
SQL> SELECT DISTINCT AVG(PerDiewExpenses) AS WITH_DISTINCT FROM Instructors;
Using the WHERE clause with group functions
Ex.
SQL> SELECT AVG(PerDiewCost) AS "AVG COST" FROM Instructors WHERE City='Toronto';
Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors GROUP BY City';
CITY Average Cost
New York 487.5
Palo Alto 750
Toronto 450
Ex.
SQL> SELECT InstructorID, City, MAX(PerDiewCost) AS "Highest Cost" FROM Instructors GROUP BY InstructorID, City;
INSTRUCTORID CITY Highest Cost
100 New York 600
110 New York 500
200 Palo Alto 750
210 Toronto 400
300 Toronto 500
Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE Country='USA' GROUP BY City;
CITY Average Cost
New York 487.5
Palo Alto 750
Using the HAVING Clause
The HAVING Clause is essentially a second WHERE clause that is evaluated after Oracle has calculated the grouping value.
Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE AVG(PerDiewCost)<500>
=> Now, we will get a Oracle error. So, we should write the query with using HAVING clause. i.e.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors GROUP BY City HAVING AVG(PerDiewCost)<500;
CITY Average Cost
New York 487.5
Toronto 450
Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE Country='USA' GROUP BY City HAVING AVG(PerDiewCost)<500;
CITY Average Cost
New York 487.5