[go: up one dir, main page]

0% found this document useful (0 votes)
20 views7 pages

Exp 4

The document provides an overview of SQL single row functions, categorized into number, character, date, and conversion functions. It includes examples of each function with syntax and expected outputs using the dual table. Additionally, it explains the purpose of each function, such as ABS, CEIL, INITCAP, and TO_CHAR, among others.

Uploaded by

damisettilohitha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
20 views7 pages

Exp 4

The document provides an overview of SQL single row functions, categorized into number, character, date, and conversion functions. It includes examples of each function with syntax and expected outputs using the dual table. Additionally, it explains the purpose of each function, such as ABS, CEIL, INITCAP, and TO_CHAR, among others.

Uploaded by

damisettilohitha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

DATABASE MANAGEMENT SYSTEMS LAB

EXPERIMENT NO: 4 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.
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.

Selectsqrt(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.

Selecttrunc(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.

select greatest(5,8,1,95,72,48,22,8958,2) from dual;

CHARACTER FUNCTIONS
INITCAP ( ): This function returns the string with first letter of each word in uppercase.
Syntax: INITCAP (string1)

Selectinitcap('andhra pradesh') 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)

selectinstr('Theres no such thing as a free lunch','re',1,1) from dual;

Output: 4

selectinstr('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)

Selectlpad('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)

Selectrpad('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)

Selectltrim('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)

Selectrtrim('abcxyzabcxyz','xyz') from dual;

Output: abcxyzabc
DATE FUNCTIONS
SYSDATE: This function returns current date of system.

Selectsysdate 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)

Selectadd_months('15-aug-1947',12) from dual;

Result:15-AUG-48

Selectadd_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’)

Selectnext_day('15-aug-1947','sun') from dual;

Result: 17-AUG-47

Selectnext_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)

Selectlast_day('15-aug-1947') from dual;

Result:31-AUG-47

Selectlast_day('22-apr-2017') from dual;

Result:30-APR-17
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 ():

Selectto_char(30000,'$99999') from dual;

Result: $30000

Selectto_char(sysdate,'day,month year') from dual;

Result:tuesday,july twenty seventeen

selectto_char(sysdate,'dd,monyyyy') from dual;

Result:11,jul 2017

TO_DATE ():

Selectto_date('25 january,17') from dual;

Result:25-JAN-17

Selectto_date('2 january,17') from dual;

Result:02-JAN-17

TO_NUMBER ():

Selectto_number('1210.72','9999.99') from dual;

Result: 1210.72

You might also like