1 Aditya College of Engineering & Technology
DATABASE MANAGEMENT SYSTEMS LAB
EXPERIMENT NO: 1 DATE:
SQL Single row functions
Single row functions can be categorized into four types, single row functions are applied for each
row and produces individual output for each row.
1. Number functions
2. Character functions
3. Date functions
4. Conversion functions
Dual Table: dual is a default table, created at the time of oracle installation.
Regd. No:
Database Management Systems Lab
2 Aditya College of Engineering & Technology
Number functions
ABS (): Absolute is the measure of the magnitude of a value. This function returns absolute value is
always a positive number.
select abs(-9) from dual;
Output: 9
CEIL (): This function returns largest integer greater than or equal to n.
select ceil(18.23) from dual;
Output: 19
FLOOR (): This function returns the smallest integer equal to or less than n.
select floor(18.23) from dual;
Output: 18
SQRT (): This function gives the square root of the given value n.
Select sqrt(576) from dual;
Output: 24
MOD (): This function gives the remainder when m is divided by n.
select mod(17,5) from dual;
Output: 2
POWER (): This function gives the value of m raised to the power of n.
select power(3,3) from dual;
Output: 27
ROUND (): This function rounds the number to the given number of digits of precision
select round(14.5264,2) from dual;
Output: 14.53
TRUNC (): This function truncates the decimal portion. This function truncates (deletes) m decimal to
n decimal places.
Select trunc(10.10998998,4) from dual;
Output: 10.1099
LEAST (): This function returns least integer from a set of integers.
select least(5,8,1,95,72,48,22,8958,2) from dual;
Output: 1
GREATEST (): This function returns GREATEST integer from a set of integers.
Regd. No:
Database Management Systems Lab
3 Aditya College of Engineering & Technology
CHARACTER FUNCTIONS
INITCAP ( ): This function returns the string with first letter of each word in uppercase.
Syntax: INITCAP (string1)
Select initcap('andhra prasad') from dual;
Output: Andhra Prasad
LOWER ( ): This function returns the string in lower case.
Syntax: LOWER (string1)
select lower('THE PEN IS MIGHTIER THAN THE SWORD') from dual;
Output: the pen is mightier than the sword
UPPER ( ): This function returns the string in upper case.
Syntax: UPPER (string1)
select upper('the pen is mightier than the sword') from dual;
Output: THE PEN IS MIGHTIER THAN THE SWORD
CONCAT ( ): This function returns a string by appending string1 with string2.
Syntax: CONCAT (string1, string2)
Select concat('hello','every one') from dual;
Output: helloevery one
LENGTH ( ): This function gives length of the given string.
Syntax: LENGTH (string)
select length('Fortune favors the bold') from dual;
Output: 23
SUBSTR ( ): This function returns a portion of a string beginning at the character position.
Syntax: SUBSTR (STRING, POSITION, OFFSET)
select substr('Theres no such thing as a free lunch',10,5) from dual;
Output: such
INSTR ( ): This function returns Nth occurrence of string2 (first character position) in string1. In
string1 characters index begins from 0. This function starts searching from Mth position.
Syntax: INSTR (STRING1, STRING2, M, N)
select instr('Theres no such thing as a free lunch','re',1,1) from dual;
Output: 4
Regd. No:
Database Management Systems Lab
4 Aditya College of Engineering & Technology
select instr('Theres no such thing as a free lunch','re',1,2) from dual;
Output: 28
TRANSLATE ( ): This function returns a string after replacing some set of characters into another set.
Syntax: TRANSLATE (MAIN STRING, FROM_STRING, TO_STRING)
select translate('delhi is the capital of india','i','a') from dual;
Output: delha as the capatal of andaa
select translate('Theres no such thing as a free lunch','res','12@') from dual;
Output: Th212@ no @uch thing a@ a f122 lunch
LPAD ( ): This function returns a string as output after padding string2 to the left side of string1 to n
length.
Syntax: LPAD (STRING1, N, STRING2)
Select lpad('india',20,'$') from dual;
Output: $$$$$$$$$$$$$$$india
RPAD ( ): This function returns a string as output after padding string2 to the right side of string1 to n
length.
Syntax: RPAD (STRING1, N, STRING2)
Select rpad('india',20,'&') from dual;
Output: india&&&&&&&&&&&&&&&
LTRIM ( ): This function returns a string as output after trim string2 from left side up to the string1
which is not in set.
Syntax: LTRIM (STRING1, STRING2)
Select ltrim('abcxyzabcxyz','abc') from dual;
Output: xyzabcxyz
RTRIM ( ): This function returns a string as output after trim string2 from right side up to the string1
which is not in set.
Syntax: RTRIM (STRING1, STRING2)
Select rtrim('abcxyzabcxyz','xyz') from dual;
Output: abcxyzabc
Regd. No:
Database Management Systems Lab
5 Aditya College of Engineering & Technology
DATE FUNCTIONS
SYSDATE: This function returns current date of system.
Select sysdate from dual;
Result: 11-JUL-17
ADD_MONTHS (): This function returns date d plus n months, i.e adds n months to the given date d.
Syntax: ADD_MONTHS (DATE, NO_OF_MONTHS)
Select add_months('15-aug-1947',12) from dual;
Result: 15-AUG-48
Select add_months('01-may-2017',15) from dual;
Result: 01-AUG-18
MONTHS_BETWEEN (): This function returns difference between given two dates.
Syntax: MONTHS_BETWEEN (DATE1, DATE2)
Select months_between('19-SEP-16','17-MAY-16') from dual;
Result: 4.06451613
Select months_between('19-FEB-16','17-MAY-16') from dual;
Result: -2.9354839
NEXT_DAY (): This function returns the date of the next weekday from the date specified.
Syntax: NEXT_DAY (DATE, ‘WEEKDAY’)
Select next_day('15-aug-1947','sun') from dual;
Result: 17-AUG-47
Select next_day('25-jul-17','sun') from dual;
Result: 30-JUL-17
LAST_DAY (): This function returns the date of the last day of the month.
Syntax: LAST_DAY (DATE)
Select last_day('15-aug-1947') from dual;
Result: 31-AUG-47
Select last_day('22-apr-2017') from dual;
Result: 30-APR-17
Regd. No:
Database Management Systems Lab
6 Aditya College of Engineering & Technology
CONVERSION FUNCTIONS
SQL provides three functions to convert a value from one data type to another
1. TO_CHAR (number | date * , ‘fmt’+ ): Converts a number or a date value to a VARCHAR2
character string with format model fmt.
2. TO_NUMBER (char * , ‘fmt’ + ): Converts a character string containing digits to a number with
the optional format model fmt.
3. TO_DATE (char * , ‘fmt’ + ): Converts a character string representing a date to a date value
according to the fmt specified (If fmt is omitted, format is DD-MONYY. )
TO_CHAR ():
Select to_char(30000,'$99999') from dual;
Result: $30000
Select to_char(sysdate,'day,month year') from dual;
Result: tuesday, july twenty seventeen
select to_char(sysdate,'dd,mon yyyy') from dual;
Result: 11,jul 2017
TO_DATE ():
Select to_date('25 january,17') from dual;
Result: 25-JAN-17
Select to_date('2 january,17') from dual;
Result: 02-JAN-17
TO_NUMBER ():
Select to_number('1210.72','9999.99') from dual;
Result: 1210.72
Regd. No:
Database Management Systems Lab
