SQL Functions

Please refer the below mentioned link for SQL FUNCTIONS. This is an excellent link for getting deep insight of all available SQL FUNCTIONS.

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893

There are different types of SQL functions:

• single_row_function


1. numeric_function


2. character_function


3. data_mining_function


4. datetime_function
   


5. conversion_function


6. collection_function


7. XML_function


8. miscellaneous_single_row_function


• aggregate_function


• analytic_function


• object_reference_function
  


• model_function


• user_defined_function

Some of the SQL Functions with examples are as below:

LOWER(string) Converts a string to all lowercase characters

SELECT LOWER('MCMILLAN') "Lowercase" FROM DUAL;

INITCAP(string) Converts a string to initial capital letters

SELECT INITCAP('the apple') "Capitals" FROM DUAL;

UPPER(string) Converts a string to all uppercase characters

SELECT UPPER(last_name) "Uppercase" FROM emp;

LENGTH(string) Returns the number of characters in a string

SELECT LENGTH('SACHIN') "Length in characters" FROM DUAL;

AVG(expression) Returns the average of the values in a set of rows

SELECT AVG(salary) "Average" FROM emp;

Average

--------

6400

COUNT(expression) or COUNT(*)

Returns the number of rows in the set

If you include an expression, COUNT returns only the number of rows in which the expression is not null.

COUNT(*) counts all rows.

MAX(expression) Returns the largest value from a set of rows

SELECT MAX(salary) "Maximum" FROM emp;

Maximum

----------

29000

MIN(expression) Returns the smallest value from a set of rows

SELECT MAX(salary) "Maximum" FROM emp;

SUM(expression) Adds the value for all rows in the query or for all rows with the same values for columns listed in the GROUP BY clause

SELECT SUM(salary) "Total" FROM emp;

Total

----------

67140

ABS(number) Removes the sign, if any, returning a positive value

The following example returns the absolute value of -15:

SELECT ABS(-5) "Absolute" FROM DUAL;

Absolute

----------

5

GREATEST(value1,value2, …)

Returns the largest of the values in the list

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')
"Greatest" FROM DUAL;

This function is used for multiple values in the same row.

Greatest

--------

HARRY

LEAST(value1,value2, …)

Returns the smallest of the values in the list. This function is used for multiple values in the same row.

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL;

LEAST

------

HAROLD

ROUND(number, decimal places)

Rounds a value to the specified number of decimal places

The following example rounds a number to one decimal point:

SELECT ROUND(18.193,1) "Round" FROM DUAL;

Round

----------

18.2

The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

Round

----------

20

The following examples illustrate the difference between rounding NUMBER and floating-point number values. NUMBER values are rounded up (for positive values), whereas floating-point numbers are rounded toward the nearest even value:

SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;

ROUND(1.5) ROUND(2.5)

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

2 3

SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

ROUND(1.5F) ROUND(2.5F)

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

2.0E+000 2.0E+000

TRUNC(number,decimal places)

Cuts off a value at the specified number of decimal places

The following examples truncate numbers:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

Truncate

----------

15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

Truncate

----------

10

SUBSTR(string, starting value, number of characters)

Extracts a portion of a string

If the starting value is 0, it is treated as 1. If the starting-value is negative, Oracle counts backward from the end of the string. If the starting value is positive, Oracle counts forward from the beginning of the string.

SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;

Substring

---------

CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;

Substring

---------

CDEF

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL;

Substring with bytes

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

CD

ADD_MONTHS(date, number of months)

Adds the specified number of months to the date value

(subtracts months if the number of months is negative)

If the result would be a date beyond the end of the month, Oracle returns the last day of the resulting month.

The following example returns the month after the hire_date in the sample table employees:

SELECT TO_CHAR(

ADD_MONTHS(hire_date,1),

'DD-MON-YYYY') "Next month"

FROM employees

WHERE last_name = 'Baer';

Next Month

-----------

07-JUL-1994

LAST_DAY(date) Returns the last day of the month that contains the date

The following statement determines how many days are left in the current month.

SELECT SYSDATE,

LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

SYSDATE Last Days Left

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

30-MAY-01 31-MAY-01 1

MONTHS_BETWEEN(date1,date2)

Returns the difference between two dates expressed as whole

and fractional months

If date1 is earlier than date2, the result is negative.

The result also takes into account time differences between the two values.

The following example calculates the months between two dates:

SELECT MONTHS_BETWEEN

(TO_DATE('02-02-1995','MM-DD-YYYY'),

TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

FROM DUAL;

Months

----------

1.03225806


NEXT_DAY(date, day name)

Returns the date of the first day of the specified name that is

later than the date supplied

This example returns the date of the next Tuesday after February 2, 2001:

SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"

FROM DUAL;

NEXT DAY

-----------

06-FEB-2001

ROUND (datetime, format)

Returns the date-time rounded to the unit specified by the

format, or to the nearest day if no format is supplied

Note: For details on available formats, see the full

description of functions (below).

The following example rounds a date to the first day of the following year:

SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR')

"New Year" FROM DUAL;

New Year

---------

01-JAN-01

SYSDATE Returns the current date-time from the server where the database is located

The following example returns the current operating system date and time:

SELECT TO_CHAR

(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"

FROM DUAL;

NOW

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

04-13-2001 09:45:51


TRUNC(datetime) Removes the time component from a date-time value. The following example truncates a date:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')

"New Year" FROM DUAL;

New Year

---------

01-JAN-92

TO_CHAR(date, format)

Converts a date to a string in the specified format

TO_CHAR(number, format)

Converts a number to a string in the specified format.

The following statement uses implicit conversion to combine a string and a number into a number:

SELECT TO_CHAR('01110' + 1) FROM dual;

TO_C

----

1111

TO_DATE(string, format)

Converts a string to a date using the specified format.

The following example converts a character string into a date:

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

TO_DATE('

---------

15-JAN-89

TO_NUMBERstring, format)

Converts a string to a number using the optional format if specified.

The following examples convert character string data into a number:

UPDATE employees SET salary = salary +

TO_NUMBER('100.00', '9G999D99')

WHERE last_name = 'Perkins';