SQL Functions

Like as any other programming language, SQL also supports a rich set of in-build functions. These functions greatly increase the ability to manipulate the information stored in a database. These functions are also referred as Oracle Functions.

SQL functions accept arguments as an input and returns result as an output Arguments can be some constant values, or expressions, or column names related to tables. The general format of the SQL function is as given below:

Function_name(argument1, argument2,…,argument)

SQL functions can be categorized into two parts, based on whether they operate on a single row or a set or rows.

1.     Aggregate functions (Group function)

2.     Scalar functions (Single Row function)

-         Functions that operate on a single row are called Scalar function or Single Row functions.

-         These function return one row (result)  for every row given as an input. So, if five rows are given as an input, there will be five results as an output.

-         For example, LENGTH is a Scalar function. If finds length of a given string and returns individual results for each row given as an input.

-         The scalar functions can further be divided into sub-groups based on the data type on which they operate, as given below:

1.     Numeric function

2.     Character function

3.     Date functions

4.     Conversion function

5.     Miscellaneous function

These functions are explained in following sub-sections.

1.    Numeric functions

Various numeric functions are described below. These functions are also referred as Arithmetic functions.

These functions are scalar or single row functions. So, in contrast to aggregate functions, constant values can also be passed as arguments to these functions. These functions return individual results for each row passed as an input.

Only constant arguments are considered in examples given here. But, any column having numeric data type can be passed as an argument to these functions.

For sake of simplicity, column headers have been omitted while displaying outputs. For each functions, syntax, description, example and output are described.

List types of 14 Numeric function

1.     ABS                                                                

2.     SQRT

3.     POWER

4.     MOD

5.     CEIL

6.     FLOOR

7.     ROUND

8.     TRUNC

9.     EXP

10.  LN

11.  LOG

12.  COS, SIN, TAN

13.  COS, SINH, TANH

14.  SIGN

 

SQL

1.     ABS

ABS ( n )

Returns absolute value of ‘n’. This means, negative numbers are converted to positive numbers.

Here, ‘n’ can be a constant number, or some expression, or column name having numeric data type.

EX:- SELECT ABS (-25), ABS(25) FROM dual;

output:-              25                   25

2.     SQRT

SQRT ( n )

Returns square root of ‘n’.

Here, ‘n’ cannot be a negative number.

EX:- SELECT SQRT (25), SQRT (30) FROM dual;

Output:-              5          5.47722558

3.     POWER

POWER ( m, n )

Returns m raised to n^th power, i.e., returns mⁿ.

Also, n must be and integer value.

EX:- SELECT POWER (3,2), POWER(4,3) FROM dual;

Output:-              9                      64

4.     MOD

MOD (m , n )

Returns remainder of a m divided by n operation.

EX:- SELECT MOD (5,2), MOD (5,3) FROM dual;

Output:-              1                      2

5.     CEIL

CETL( n)

Returns the largest integer value that is greater than or equal to n.

EX:-SELECT CEIL (25.2), CEIL (25.7), CEIL(-25.2) FROM dual;

Output:-              26                   26                   25

6.     FLOOR

FLOOR ( n )

Returns the smallest integer value that is less than or equal to n.

EX:-SELECT FLOOR (25.2), FLOOR (25.7),  FLOOR (-25.2) FROM dual;

Output:-              25                   25                   24

7.     ROUND

ROUND ( m, n)

Returns m, rounded to n places to the right of a decimal point.

If n is omitted, m is rounded to 0 places.

If n is negative, m is rounded to n places to the left of a decimal point.

N must be an integer value.

EX:-SELECT ROUND (157.732,2), ROUND (157.732), ROUND (157.732, -2) FROM dual;

Output:-              157.73                      157                             200

8.     TRUNC

TRUNC (m, n)

If n is positive, m is truncated to n places to the right of a decimal point.

If n is omitted, m is truncated to 0 places.

If n is negative, m is truncated to n places to the left of a decimal point.

EX:- SELECT TRUNC (157.732, 2), TRUNC (157.732), TRUNC (157.732, -2) FROM dual;

Output:-              157.73                      157                 100

ratan tata history and cheapest car making

9.     EXP

EXP (n)

Returns e raised to nth power, i.e., returns en, where e = 2.071828183.

EX:- SELECT EXP (2), EXP (3) FROM dual;

Output:- 7.3890561               20.0855369

10.            LN

LN (n)

Returns natural, or base e, logarithm of n.

EX:- SELECT LN (10), LN(2) FROM dual;

Output:- 2.30258509                        0.693147181

11.            LOG

LOG (b , n)

Returns logarithm of the n in the base of b, or return Log _b n.

EX:- SELECT LOG(10.5), LOG(10, 100) FROM dual;

Output:-              0.698970004                      2

12.            COS, SIN, TAN

COS (n), SIN (n), TAN (n)

Returns trigonometric cosine, sine and tangent values for n; where n is an angle in radius.

EX:- SELECT COS ( 3.1415), SIN (3.1415), TAN (3.1415) FROM dual;

Output:-              -1                    0.000092654          -0.00009265

13.            COSH, SINTH, TANH

COSH ( n ), SINH ( n ), TANH ( n )

Returns hyperbolic cosine, sine and tangent values for n; where n is in radius.

EX:- SELECT COSH ( 3.1415), SINH (3.1415),  TANH (3.1415) FORM dual;

Output:- 11.5908833             11.5476654                        0.996271387

14.            SIGN

SIGN ( n )

Returns -1, if n is negative; 0 if n is zero; and 1 if n is positive.

EX:-SELECT SIGN (-25), SIGN ( 0 ), SIGN (25) FROM dual;

Output:-              -1                    0                      1

2.    Character functions

Various character or string related functions are described in this section.

Like as numeric functions, these functions are also scalar or single row functions. So, in contrast to aggregate functions, constant strings can be passed as arguments to these functions. Constant strings need to be enclosed within single quotes.

In most of the examples, only constant strings are considered here as an argument. And, for the sake of simplicity, column headers have been omitted while displaying outputs.

12 List types of Character functions

1.     LENGTH

2.     LOWER

3.     UPPER

4.     INITCAP

5.     SUBSTR

6.     LPAD

7.     RPAD

8.     LTRIM

9.     RTRIM

10.                  TRANSLATE

11.                  REPLACE

12.                  ASCII

1.     LENGTH

LENGTH (str)

Returns length, i.e. number of characters, of str.

Here, str represents a string, which can be a constant string or column name having character data type.

EX:- SELECT LENGTH (‘india’) FROM dual;

Output:- 5

 consider another example in which ename and city columns of Employee are passed as an argument in LENGTH function.

2.     LOWER

LOWER (str)

Returns str with all letters in lower case.

EX:- SELECT LOWER (‘SACHIN RAMESH tendulkar’)  FROM dual;

Output: Sachin ramesh tendulkar

3.     UPPER

UPPER (str)

Returns str with all letters in upper case.

EX:- SELECT UPPER (‘SACHIN RAMESH tendullkar’) FROM dual;

Output:- SACHIN RAMESH TENDULKAR

4.     INITCAP

INITCAP (str)

Returns str with the first letter of each word in upper case.

EX:- SELECT INITCAP (‘SACHIN RAMESH tendulkar’) FROM dual;

Output:- sachin Ramesh Tendulkar

5.     SUBSTR

SUBSTR ( str, pos, length)

Returns a portion of str, beginning at pos and going up to length characters.

The first position in the string is 1.

The length is the number of characters to extract.

EX:- SELECT SUBSTR (‘SACHIN Ramesh tendulkar’,8,6) FROM dual;

Output:- Ramesh

6.     LPAD

LPAD (str, n, str2)

Returns str, left padded with str2 up to length n.

Str defaults to blank.

EX:- SELECT LPAD (‘india’, 10, ‘*’) FROM dual;

Output:- *****India

7.     RPAD

RPAD (str, n, str2)

Returns str, right padded with str2 up to length n.

Str defaults to blank.

EX:- SELECT RPAD (‘india’, 10, ‘*’) FROM dual;

Output:- India*****

8.     LTRIM

LTRIM (str, set)

Removes characters from the left of str. Characters will be removed up to the first character not in set.

EX:- SELECT LTRIM (‘Sumita’,’uSae’) FROM DUAL;

Output:- Mita

9.     RTRIM

RTRIM (str, set)

Removes character from the right of str. Characters will be removed up to the first character not in set.

EX:- SELECT RTRIM (‘Sumita’, ‘tab’) FROM dual;

Output:- Sumi

10.                   TRANSLATE

TRANSLATE (str, from_set, to_set)

Characters of str that occur in from_set are translated to the corresponding characters in the to_set.

EX:- SELECT TRANSLATE (‘abc12efg3’, ‘1234’, XYZW’) FROM dual;

Output:- abcXYefgZ

11.                   REPLACE

REPLACE (str, from_str, to_str)

Replace is similar to translate, but it works on strings rather than set of characters.

It replaces entire sub-string instead of individual characters as in translate.

Sub-string of str that occurs in from_Str is replaced with that to to_str.

EX:-SELECT REPLACE (‘abc123efg’, ‘123’, ‘XYZ’) FROM dual;

Output:- abcXYZefg

12.                   ASCII

ASCII (char)

Returns the ASCII code of a char.

EX:- SELECT ASCII (‘a’), ASCII(‘A’) FROM dual;

Output:- 97                       65

3.    Conversion functions

Oracle supports many conversion functions. The following three are the most commonly used conversion functions. They are used to convert value from one data type to another.

These functions are described below according to their conversion type.

animals extinct

List types 4 of Conversion function

1.     Converting Character to Number

2.     Converting Number to Character

3.     Converting Date to Character

4.     Converting Character to Date

1.     Converting Character to Number

TO_NUMBER:

TO_NUMBER (str)

Converts a value of a character data type, expressing a number, to NUMBER data type, i.e. converts CHAR or VARCHAR2 to NUMBER.

Returns equivalent numeric value to str.

Str must consist of 0-9, decimal point, and ‘-‘ or ‘+’ sign.

EX:- SELECT TO_NUMBER (‘1234.5’) FROM dual;

Output:- 1234.56

2.     Converting Number to Character

TO_CHAR:

TO_CHAR (n, format)

Converts a numerical value n to a character data type, using optional format.

EX:- SELECT TO_CHAR (123456, ‘0999999’) FROM DUAL;

Output:- 0123456

3.     Converting Date to Character

TO_CHAR:

TO_CHAR (date, format)

Converts a DATE value date to a CHAR value, using format.

A format must be a valid date format.

If format is omitted, the default format ‘DD-MON-YY’ is used.

Date constant, such as ’14-FEB-13’, cannot be used as an argument with this function, even though it is in default DATE format.

EX:- SELECT TO_CHAR (SYSDATE, ‘DD Month, YYYY’) FORM dual;

Output:- 31 December, 2012

4.     Converting Character to Date

TO_DATE:

TO_DATE (str, format)

Converts a character value, i.e. str, to a DATE value.

A format specifies the date format in which str contains value.

The resultant date will be in the default date format – ‘DD-MON-YY’.

If the str is in default format, then format can be omitted.

EX:- SELECT TO_DATE (’31 December, 2012’, DD Month, YYYY’) FROM dual;

Output:- 31-DEC-12

4.    Date functions

Various functions related to date data type are described in this section.

Like as numeric and character functions, these functions are also scalar or single row functions. So, constant date values can be passed as arguments to these functions Date should be in form of ‘DD-MON-YY’ while specifying as a constant and it should be enclosed within single quotes. A SYSDATE can also be used as an argument to these functions. SYSDATE returns the current date.

In most of the examples, SYSDATE or constant date values are passed as an argument. But, any column having DATE as a data type can be passed as an argument in these functions. Also, for the sake of simplicity, column headers have been omitted while displaying outputs.

7 Types of List of Date functions

1.     ADD_MONTHS

2.     MONTHS_BETWEEN

3.     LAST_DAY

4.     NEXT_DAY

5.     ROUND

6.     TRUNC

7.     NEW_TIME

1.     ADD_MONTHS

ADD_MONTHS ( date, n)

Returns new date after adding n months in date specified by data.

If n is a negative, then n month will be subtractecd.

EX:- SELECT ADD_MONTHS (SYSDATE, 3), ADD_MONTHS (SYSDATE, -3) FROM dual;

Output:-     31-MAR-13              30-SEP-12

2.     MONTHS_BETWEEN

MONTHS_BETWEEN (date1, date2)

Returns number of months between date1 and date2.

Subtracts date2 form date1 to find out the difference of months.

Result may be positive, negative, zero in integers as well as real numbers.

EX:- SELECT MONTHS_BETEWEEN (’31-MAR-13’, ’31-DEC-12’) FROM dual;

Output:- 3

3.     LAST_DAY

LAST_DAY (date)

Returns the last date of the month specified by date.

EX:- SELECT LAST_DAY (’14-FEB-13’) FROM dual;

Output:- 28-FEB-13

4.     NEXT_DAY

NEXT_DAY (date, day)

Returns the date of next named week-day specified by day relative to date.

EX:- SELECT NEXT_DAY (’31-JUL-13’, ‘SUNDAY’) FROM dual;

Output:- 04-AUG-13

5.     ROUND

ROUND (date, format)

Returns rounded date according to format.

A format can be any valid format.

If format is omitted, date is rounded to the next day if the time is 12.00 PM or later, i.e. noon or afternoon, or to today’s date if before noon.

EX:- SELECT ROUND (TO_DATE (’31-DEC-12 03:30:45 PM’, ‘DD-MON-YY HH:MI:SS PM’)) FROM dual;

Output:- 01-JAN-13

6.     TRUNC

TRUNC (date, format)

Returns truncated date according to format.

A format can be any valid format.

If format is omitted, date is truncated to 12 AM, i.e. very first moment of the day specified by date.

EX:- SELECT TRUNC(TO_Date (’31-DEC-12 03:30:45 PM’, ‘DD-MON-YY HH:MI:SS PM’)) FORM dual;

Output:- 31-DEC-12

7.     NEW_TIME

NEW_TIME (date, zone1, zone2)

Returns the date after converting it from time zone1 to time zone2.

The time zones are abbreviated in form of XST or XDT, where ST stands for standard time and DT stands for daylight time, while X means as given below:

A-    Atlantic, B- Berlin, C- Central, E- Eastern, H- Alaska-Hawaii

M- Mountain, N- Newfoundland, P- Pacific, Y- Yukon

GMT- Greenwich Mean Time

EX:- SELECT NEW_TIME (TO_DATE (’31-DEC-12 12:00 AM’, ‘DD-MON-YY HH:MI:SS PM’), ‘GMT’, ‘PST’) FROM dual;

Output:- 30-DEC-12

5.    Miscellaneous function

This section describes some functions that cannot be grouped with above categories, or they do not belong to some single data type.

List of 7 Miscellaneous function

1.     UID

2.     USER

3.     GREATEST

4.     LEAST

5.     DECODE

6.     NVL

7.     VSIZE

1.     UID

UID

Returns an integer value corresponding to the UserID of the user currently logged in.

EX:-SELECT UID FORM dual;

Output:- 35

2.     USER

USER

Returns the user name of the user currently logged in.

EX:-SELECT USER FROM dual;

Output:- SCOTT

3.     GREATEST

GREATEST (exp1, exp2, exp3….)

Returns the greatest expression.

Can be used with numerical, character as well as date related data.

Any number of arguments can be passed.

EX:- SELECT GREATEST ( 11, 32, 7), GREATEST (‘11’, ‘32’,’7’) FROM dual;

Output:- 32

4.     LEAST

LEAST (exp1, exp2, exp3…)

Similar to GREATEST function, but returns the least expression.

EX:- SELECT LEAST (11,32,7), LEAST (‘11’,’32’,37’) FROM dual;

Output:- 7   11

5.     DECODE

DECODE( Value, if1, then, if2, then2, …., else)

DECODE is similar to if-then-else construct in programming languages.

A value is the value to be tested. It is compared with if part, and whenever match is found, corresponding then part is returned. If match not found with any of the if, then else will be returned.

Applicable to numerical, character as well as date datatype.

EX:- SELECT DECODE (‘SATUR’, ‘SUN’, ‘HOLIDAY’, ‘SAATR’, ‘HALFDAY’, ‘FULL DAY’) FROM dual;

Output:- HALFDAY

6.     NVL

NVL (exp1, exp2)

Returns exp2, if exp1 is null.

Returns exp1, if it is not null.

Applicable to numerical, character as well as date data type.

EX:- SELECT NVL (5, 10), NVL ( null, 10) FROM dual;

Output:- 5   10

7.     VIZE

VSIZE(exp)

Returns the storage size of exp in Oracle.

EX:- SELECT VSIZE (25), VSIZE(‘India’), VSIZE (TO_DATE (’31-DEC-12’)) FROM dual;

Output:-  2              5          8

The size displayed here may vary depending upon the argument passed to this function.