What are sql functions

A function is similar to an operator in operation. A function is a name that performs a specific task. A function may or may not take values (arguments) but it always returns a value as the result. If function takes values then these values are to be given within parentheses after the function name.

Types of functions

Functions are classified based on the type of data on which they perform the operation. The following are the different types of functions available in Oracle.

# Arithmetic Functions.

# Date & Time functions.

# String functions.

# Conversion functions.

# Group functions.

Arithmetic functions perform take numeric data; date functions take date type data and string functions take strings. Conversion functions are used to convert the given value from one type to another. Miscellaneous functions perform operations on any type of data. Group functions are used to perform operations on the groups created by GROUP BY clause.

Note: Group functions or aggregate functions perform their operation on a group (a collection of rows). All the remaining functions are called as single-row functions as they return a result for each row.

# Arithmetic Functions.

ABS(value)	Absolute value of the given value.
CEIL(value)	Smallest integer larger than or equal to value
FLOOR(value)	Largest integer smaller than or equal to value
MOD(value,divisor)	Remainder of the division between the value and divisor.
POWER(value,exponent)	Value is raised to exponent.
ROUND(value[,precision])	Rounds value to precision. Precision can be negative if rounding is to be done on the left of the decimal point.
TRUNC(value[,precision])	Truncates instead of rounding. Otherwise same as ROUND.
SQRT(value)	Square root of value.
SIGN(value)	Returns 1 if value > 0, -1 if value <0 data-blogger-escaped-0="" data-blogger-escaped-if="" data-blogger-escaped-td="" data-blogger-escaped-value="0">

ROUND and TRUNC functions:
ROUND and TRUNC functions are used to round and truncate the given number to the given number of digits (either on the right or left of the decimal point). ROUND takes the leftmost digit that is being lost and accordingly adds one to the rightmost digit. TRUNC doesn’t take the leftmost digit into account. It just truncates the given number to the given precision.

select round(1047.785,2), trunc(1047.785,2) from dual;

The above command will display 1047.79 and 1047.78. This is because TRUNC doesn’t take the digits being lost into account.

Few sample values for round and trunc are:
--------------------------------------------------------
Function                     Result
--------------------------------------------------------
ROUND(1295.356,2)        1295.36

TRUNC(1295.356,2)         1295.35

ROUND(1295.356,0)        1295

ROUND(1285.356, -1)      1290

TRUNC(1285.356, -1)      1280

ROUND(1295,-2)             1300

--------------------------------------------------------

When precision in ROUND is positive then rounding takes place to the specified number of digits on the right of decimal point. For example, if precision is 2 it means round number to 2 digits on the right of the decimal point. When precision is negative; it means number is to be rounded to the left of the decimal point. In both the cases if the leftmost digit of the digits being lost is >= 5 then one is added to the rightmost digit of the digits that are retained.


Date Functions :
In Oracle DATE datatype stores date and time. At the time of storing a date If time is not given then it will be set to 0 hours, 0 minutes and 0 seconds (beginning of the day or 12:00 a.m.).

Date functions operate on values of DATE datatype. Except MONTHS_BETWEEN all date functions return DATE data type. The following is the list of DATE functions.

ADD_MONTHS(date, count)	Adds count number of months to date.
MONTHS_BETWEEN (date1, date2)	Returns number of months between date1 and date2.
LAST_DAY(date)	Returns the last day of the month in which date is.
NEXT_DAY(date, ’day’)	Gives the date of next day after the date, where day name of the week like ‘Monday’.
NEW_TIME(date, ’this’, ’other’)	Returns time in other time zone for time of this time zone.
ROUND(date)	Rounds the date depending upon the time. If time is at or after 12 hours then date is incremented. Time is always set to beginning of the day (0:0:0).
TRUNC(date)	Same as ROUND (date) but doesn’t increment date.

String Functions :

Concatenating Strings- We can concat 2 or more strings by using || as

select f_name||' '||l_name as "Full Name" from student;

Above command will display first name and last name separateby space.

Padding Strings :

A string can be made of a given length by padding either on the left using LPAD or on the right using RPAD. By default Oracle uses space to pad strings. However, it is possible to specify which character(s) should be used for padding.

Note: RPAD and LAPD truncate the given string if string has more number of characters than the given length.

In below SQL I am using some more string functions together.

SELECT f_name, lower(f_name), upper(f_name), initcap(f_name), length(f_name), lpad(f_name,10,'-'), rpad(f_name,10,'-') from student;

Output of above sql will be

F_NAME      LOWER(F_NAME)   UPPER(F_NAME)   INITCAP(F_NAME)
SURYAkant    suryakant                   SURYAKANT              Suryakant


LENGTH(F_NAME)   LPAD(F_NAME,10,'-')     RPAD(F_NAME,10,'-')
     9                              -SURYAkant                          SURYAkant-


Search sustring using instr function :

Syntax :
instr(string, position from where start searching, nth occurance)

Below sql return 1st occurance of string 'A'
SELECT FNAME, instr(f_name,'A') from student;

Below sql return 2nd occurance of string 'do' taht is 12.
select instr('How do you do','do',1,2) from dual;

Substring:

Syntax:
substr(string,  starting position, number of characters);

SELECT FNAME, substr(fname,4,10) from emp;

Eg. To extract first name from full name using substring
select name, substr( name, 1, instr(name,' ') - 1) as 'Firstname' from student;

Eg. To extract last name from full name
select name, substr(name,instr(name,' ') + 1) as 'Lastname' from student;
 
Triming of leading and trailing characters.
select ltrim( 'aabcbadxyabc','abc') as 'Left Trim', rtrim( 'aabcbadxyabc','abc') as 'Left Trim' from dual;

Left Trim     Right Trim
dxyabc           aabcbadxy

Trim is used to trim both leading and trailing spaces.

select length(' abc xyz ') Before, length(trim(' abc xyz ')) After from dual

BEFORE           AFTER
12                         8


TRANSLATE and REPLACE functions:
These two functions return the string after modifying the given string. TRANSLATE works on individual characters, whereas REPLACE replaces a string with another string.

The following two examples will make the difference clear.

REPLACE replaces every occurrence of string ‘ABC’ with string ‘PRQ’.

select replace('ABC ABAC XYZ DABC','ABC','PQR') Result from dual;

RESULT
PQR ABAC XYZ DPQR


TRANSLATE changes every occurrence of letter A with P, B with Q and C with R.

select translate('ABC ABAC XYZ DABC','ABC','PQR') Result from dual

RESULT
PQR PQPR XYZ DPQR

TO_CHAR Function:-
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.

select to_char(sysdate,'dd-mm-yyyy') Result from dual;
RESULT  
----------
20-07-2015

select sysdate,to_char(sysdate,'dd Month yyyy hh24:mi:ss') from dual;
SYSDATE    TO_CHAR(SYSDATE,'DDMONTHYYYYHH24:MI:SS')
--------   ----------------------------------------
20-07-15   20 July      2015 22:49:30


Format         Option Description
MM                 Number of the month: 10
MON               First three letters of month name: OCT
MONTH          Complete month name: OCTOBER
DDD                Day of the year since January 1st: 340
DD                  Day of the month: 16
D                     Day of the week: 5
Day                 Day fully spelled: Wednesday
YYYY              Four digits year: 1996
YY                  Two digits year: 96
YEAR             Year spelled out: NINTEEN-NINTY-SIX
HH or HH12  Hour of the day: 5
HH24             Hour of the day: 20
MI                  Minute of hour: 30
SS                  Second of minute: 30
A.M. or P.M.  Displays A.M. or P.M. depending on the time.
Fm                 Removes trailing spaces. ‘May ‘ becomes ‘May’
TH                 Suffix to number: DDTH will produce 16th
SP                 Number Spelled out: DDSP will produce THIRD for day 3.

TO_DATE function :-
TO_DATE is used to convert a CHAR type value to DATE type. If the value is in DD-MON-YY or DD-MM-YYYY format then TO_DATE is not needed because Oracle implicitly converts the value to DATE type.

When you insert a record with only date in DD-MON-YY format, time portion of the date is set to 00:00:00.

The following INSERT inserts a new row into PAYMETS table with date as well as time.

insert into payments values ( 10,to_date('14-04-2001 10:20:00', 'dd-mm-yyyy hh24:mi:ss'), 2000);

TO_NUMBER function
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

The first application of TO_NUMBER is to convert formatted number to number. The following example is trying to multiply $333 by 20. But as the number with currency symbol is not taken as a number by Oracle, it results in error.

SQL> select $333 * 20 from dual;
select $333 * 20 from dual
*
ERROR at line 1:
ORA-00911: invalid character

TO_NUMBER function can be used to convert $333 to a number so that it is treated as a number by Oracle. The format in TO_NUMBER specified that the first character is to be taken as currency symbol and remaining as digits.

SQL> select to_number('$333','$999') * 20 from dual

TO_NUMBER('$333','$999')*20
-------------------------------------------------------
6660 

Sorting strings in numeric order: -
If vno is of varchar type and have only digits in it and we want to sort the values present in this column in numeric order.

select vno from vehicles order by to_number(vno);

Decode:
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.

select fname , DECODE(GRADE, 'A','1st','B','2nd','C','3rd','Fail') from student;

Above comand prints '1st' in place of 'A', '2nd' in place of 'B', '3rd' in place of 'C'. If any other value present in GRADE column it prints 'Fail'.

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.

SELECT greatest(1,2,3), least(1,2,3) from dual;

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.

The following query is to display the details of all students. But we get nothing for NODAYS of stu_id 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.

select stu_id, stdate, enddate - stdate nodays from student;

stu_id    STDATE       NODAYS
b1           12-JAN-01       39
b2           15-JAN-01       49
b3           20-JAN-01       38
b4           02-MAR-01     28
b5           05-APR-01      35
b6           12-JUL-01
b7           15-AUG-01

However, now we want to take ending date as system date if it is NULL.

select stu_id, stdate, nvl(enddate,sysdate) - stdate nodays from student;

stu_id    STDATE      NODAYS
b1            12-JAN-01     39
b2           15-JAN-01      49
b3           20-JAN-01      38
b4           02-MAR-01    28
b5           05-APR-01     35
b6           12-JUL-01      50.17985
b7           15-AUG-01    16.17985