Single row functions
1. LOWER
The Lower function converts the character values into lowercase letters.
SELECT lower('ORACLE') FROM DUAL;
2. UPPER
The Upper function converts the character values into uppercase letters.
SELECT upper('oracle') FROM DUAL;
3. INITCAP
The Initcap function coverts the first character of each word into uppercase and the
remaining characters into lowercase.
SELECT initcap('LEARN ORACLE') FROM DUAL;
4. CONCAT
The Concat function coverts the first string with the second string.
SELECT concat('Oracle',' Backup) FROM DUAL;
5. SUBSTR
The Substr function returns specified characters from character value starting at position m
and n characters long. If you omit n, all characters starting from position m to the end are
returned.
Syntax: substr(string [,m,n])
SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;
SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;
You can specify m value as negative. In this case the count starts from the end of the string.
SELECT substr('ORACLE BACKUP',-6) FROM DUAL;
6. LENGTH
The Length function is used to find the number of characters in a string.
SELECT length('Oracle Data Guard') FROM DUAL;
7. INSTR
The Instr function is used to find the position of a string in another string. Optionally you can
provide position m to start searching for the string and the occurrence n of the string. By
default m and n are 1 which means to start the search at the beginning of the search and
the first occurrence.
Syntax: instr('Main String', 'substring', [m], [n])
SELECT instr('oralce apps','app') FROM DUAL;
SELECT instr('oralce apps is a great application','app',1,2) FROM
DUAL;
8. LPAD
The Lpad function pads the character value right-justified to a total width of n character
positions.
Syntax: lpad(column, n, 'string');
SELECT lpad('100',5,'x') FROM DUAL;
9. RPAD
The Rpad function pads the character value left-justified to a total width of n character
positions.
Syntax: rpad(column, n, 'string');
SELECT rpad('100',5,'x') FROM DUAL;
10. TRIM
The Trim function removes the leading or trailing or both the characters from a string.
Syntax: trim(leading|trailing|both, trim_char from trim_source)
SELECT trim('O' FROM 'ORACLE') FROM DUAL;
11. REPLACE
The Replace function is used to replace a character with another character in a string.
Syntax: replace(column, old_char,new_char)
SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;
Number Functions Example
1. ROUND
The Round function rounds the value to the n decimal values. If n is not specified, there
won't be any decimal places. If n is negative, numbers to the left of the decimal point are
rounded.
Syntax: round(number,n)
SELECT round(123.67,1) FROM DUAL;
SELECT round(123.67) FROM DUAL;
SELECT round(123.67,-1) FROM DUAL;
2. TRUNC
The Trunc function truncates the value to the n decimal places. If n is omitted, then n
defaults to zero.
Syntax: trunc(number,n)
SELECT trunc(123.67,1) FROM DUAL;
SELECT trunc(123.67) FROM DUAL;
3. MOD
The Mod function returns the remainder of m divided by n.
Syntax: mod(m,n)
SELECT mod(10,5) FROM DUAL;
Date Functions Example
1. SYSDATE
The Sysdate function returns the current oracle database server date and time.
SELECT sysdate FROM DUAL;
2. Arithmetic with Dates
You can add or subtract the number of days or hours to the dates. You can also subtract
the dates
SELECT sysdate+2 "add_days" FROM DUAL;
SELECT sysdate-3 "sub_days" FROM DUAL;
SELECT sysdate+3/24 "add_hours" FROM DUAL;
SELECT sysdate-2/24 "sub_hours" FROM DUAL;
SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns
number of days between the two dates.
3. MONTHS_BETWEEN
The Months_Between function returns the number of months between the two given dates.
Syntax: months_between(date1,date2)
SELECT months_between(sysdate,hire_date) FROM EMPLOYEES:
SELECT months_between('01-JUL-2000', '23-JAN-2000') FROM DUAL;
4. ADD_MONTHS
The Add_Months is used to add or subtract the number of calendar months to the given
date.
Syntax: add_months(date,n)
SELECT add_months(sysdate,3) FROM DUAL;
SELECT add_months(sysdate,-3) FROM DUAL;
SELECT add_months('01-JUL-2000', 3) FROM DUAL;
5. NEXT_DAY
The Next_Day function finds the date of the next specified day of the week. The syntax is
NEXT_DAY(date,'char')
The char can be a character string or a number representing the day.
SELECT next_day(sysdate,'FRIDAY') FROM DUAL;
SELECT next_day(sysdate,5) FROM DUAL;
SELECT next_day('01-JUL-2000', 'FRIDAY') FROM DUAL;
6. LAST_DAY
The Last_Day function returns the last day of the month.
SELECT last_day(sysdate) FROM DUAL;
SELECT last_day('01-JUL-2000') FROM DUAL;
7. ROUND
The Round function returns the date rounded to the specified format. The Syntax is
Round(date [,'fmt'])
SELECT round(sysdate,'MONTH') FROM DUAL;
SELECT round(sysdate,'YEAR') FROM DUAL;
SELECT round('30-OCT-85','YEAR') FROM DUAL;
8. TRUNC
The Trunc function returns the date truncated to the specified format. The Syntax is
Trunc(date [,'fmt'])
SELECT trunc(sysdate,'MONTH') FROM DUAL;
SELECT trunc(sysdate,'YEAR') FROM DUAL;
SELECT trunc('01-MAR-85','YEAR') FROM DUAL;
The Oracle Conversion and General Functions are covered in other sections. Go through
the links Oracle Conversion Functions and Oracle General Functions
Multi-row SQL functions
Multi-row SQL functions (also called group or aggregate functions) work with groups of rows.
These functions ignore NULL values, except where noted in this section. The most commonly
used multi-row SQL functions fall into the numeric group. All functions listed in this section are
numeric functions.
avg()
The avg() function returns the average value of a numeric field from a group of rows. For
example,
SELECT avg (base_salary)
FROM EMPLOYEES;
returns the average salary of all employees.
count()
The count() function counts the number of rows in a group of rows. This function counts all
rows in the group, including those for which a NULLvalue is present. There are two ways of
calling count(), as follows:
SELECT count (*)
FROM EMPLOYEE_HISTORY
WHERE employee_number = 90213
AND warning = 'Y';
SELECT count (married)
FROM EMPLOYEE_HISTORY
WHERE employee_number = 90213
AND warning = 'Y';
The first example returns the total number of rows that match the querys WHERE clause. The
second example returns the total number of rows that have a non-NULL value in the specified
column.
max()
The max() function returns the highest value of a specified column from a group of rows. For
example,
SELECT max (base_salary)
FROM EMPLOYEES;
returns the salary of the highest paid employee.
min()
The min() function returns the lowest value of a specified column from a group of rows. For
example,
SELECT min (base_salary)
FROM EMPLOYEES;
returns the salary of the lowest paid employee.
sum()
The sum() function returns the total of all values for a specified column in a group of rows. For
example,
SELECT sum (vacation_days_used)
FROM EMPLOYEES;
returns the total number of vacation days taken by employees this year
