**SQL Functions**

SQL functions are similar to SQL operators in that both manipulate data items and both return a result. SQL functions differ from SQL operators in the format in which they appear with their arguments. The SQL function format enables functions to operate with zero, one, or more arguments.

function(argument1, argument2, ...) alias

SQL functions are used exclusively with SQL commands within SQL statements. There are two general types of SQL functions: single row (or scalar) functions and aggregate functions. These two types differ in the number of database rows on which they act. A single row function returns a value based on a single row in a query, whereas an aggregate function returns a value based on all the rows in a query.

**SQL arithmetic functions are :**

Functions Description

abs()

This SQL ABS() returns the absolute value of a number passed as argument.

ceil()

This SQL CEIL() will rounded up any positive or negative decimal value within the function upwards.

floor()

The SQL FLOOR() rounded up any positive or negative decimal value down to the next least integer value.

exp()

The SQL EXP() returns e raised to the n-th power(n is the numeric expression), where e is the base of natural algorithm and the value of e is approximately 2.71828183.

ln()

The SQL LN() function returns the natural logarithm of n, where n is greater than 0 and its base is a number equal to approximately 2.71828183.

mod()

This SQL MOD() function returns the remainder from a division.

power()

This SQL POWER() function returns the value of a number raised to another, where both of the numbers are passed as arguments.

sqrt()

The SQL SQRT() returns the square root of given value in the argument.

**SQL Character Functions:**

Character functions that return character values return values of the same datatype as the input argument. The length of the value returned by the function is limited by the maximum length of the datatype returned.

• For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.

• For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

1.CHR:

Returns the character with the binary equivalent to n in the database character set.

Syntax:

CHR (n)

Example:

SELECT CHR(68)||CHR(79)||CHR(71) "Dog" FROM DUAL;

Returns the following result:

Dog

---

DOG

2.CONCAT

Returns char1 concatenated with char2, where char1 and char2 are string arguments. This function is equivalent to the concatenation operator (||).

Syntax:

CONCAT(char1, char2)

Example

This example uses nesting to concatenate three character strings:

SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"

FROM emp

WHERE empno = 7900;

3.INITCAP

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Syntax

INITCAP(char)

Example

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

Returns the following result:

Capitals

--------

The Soap

4.LOWER

Returns a string argument char, with all its letters in lowercase. The return value has the same datatype as char, either CHAR or VARCHAR2.

Syntax:

LOWER(char)

Example

SELECT LOWER('LOWER') FROM DUAL;

Returns the following result:

LOWER

-----

lower

ODBC Function

{fn LCASE (char)}

5.LPAD

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.

Syntax

LPAD(char1,n [,char2])

Example

SELECT LPAD('Page1',15,'*.') "LPAD example" FROM DUAL;

Returns the following result:

LPAD example

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

*.*.*.*.*.Page1

6.LTRIM

Returns the string argument char, with its left-most characters removed up to the first character which is not in the string argument set, which defaults to (a single space).

Syntax:

LTRIM(char [, set])

Example:

SELECT LTRIM ('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;

Returns the following result:

LTRIM example

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

XxyLAST WORD

ODBC Function

{fn LTRIM (char) } (trims leading blanks)

7.RPAD

Returns char1, right-padded to length n with char2 replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.

Syntax:

RPAD(char1,n [,char2 ])

Example:

SELECT RPAD('ename',12,'ab') "RPAD example"

FROM emp

WHERE ename = 'TURNER';

Returns the following result:

RPAD example

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

enameabababa

8.RTRIM

Returns the string argument char, with its right-most characters removed following the last character which is not in the string argument set. This defaults to ' ' (a single space).

Syntax

RTRIM(char [,set])

Example

SELECT RTRIM ('TURNERyxXxy', 'xy') "RTRIM example" FROM DUAL;

Returns the following result:

RTRIM examp

-----------

TURNERyxX

9.SUBSTR

Returns a portion of the string argument char, beginning with the character at position m and n characters long.

Syntax:

SUBSTR(char, m [, n ])

Notes:

If m is positive, SUBSTR counts from the beginning of char to find the first character. If m is negative, SUBSTR counts backwards from the end of char. The value m cannot be 0. If n is omitted, SUBSTR returns all characters to the end of char. The value n cannot be less than 1.

Example

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

10.TRANSLATE

Syntax:

TRANSLATE(char, from, to)

Returns char with all occurrences of each character in from replaced by its corresponding character in to, where char, from, and to are string arguments.

Example

SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;

Returns the following result:

Licence

-------

9XXX999

11. TRIM

Syntax:

TRIM( [[<trim_spec >] char ]

FROM ] string )

If <trim_spec> is omitted, then BOTH is implied. If char is omitted, then a space character is implied.

Purpose

Removes leading and/or trailing blanks (or other characters) from a string.

Example

SELECT TRIM ('OLD' FROM 'OLDMAN') FROM DUAL;

Returns the following result:

TRIM('

------

MAN

12.UPPER

Syntax

UPPER(char)

Returns the string argument char with all its letters converted to uppercase. The return value has the same datatype as char.

Example

SELECT UPPER('Carol') FROM DUAL;

Returns the following result:

UPPER

-----

CAROL

Aggregate functions in SQL:

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

This function can produced a single value for an entire group or table.They operate on sets of rows and return results based on groups of rows.

The general syntax for most of the aggregate function is as follows:

aggregate_function( [ DISTINCT | ALL ] expression)

List of SQL Aggregate functions are :

AVG:

Syntax

AVG([DISTINCT | ALL] n)

Returns the average value of a column n.

Example :

SELECT AVG(SAL) FROM EMP;

Returns the following result:

AVG(SAL)

---------

2073.21

COUNT:

Syntax:

COUNT([* | [DISTINCT | ALL] expr})

Returns the number of rows in the query.

Example :

SELECT COUNT(*) "Total" FROM emp;

Returns the following result:

Total

----------

14

MAX:

Syntax:

MAX([DISTINCT | ALL] expr)

Returns the maximum value of an expression specified by the argument expr.

Example

SELECT MAX(SAL) FROM EMP;

Returns the following result:

MAX(SAL)

---------

5000

MIN:

Syntax:

MIN([DISTINCT | ALL] expr)

Returns the minimum value of an expression specified by the argument expr.

Example:

SELECT MIN(SAL), MAX(SAL) FROM EMP;

Returns the following result:

MIN(SAL)

---------

800

SUM:

Syntax:

SUM([DISTINCT | ALL] n)

Returns the sum of values of n.

Example

SELECT deptno, SUM(sal) TotalSalary FROM emp GROUP BY deptno;

Returns the following result:

DEPTNO TOTALSALARY

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

10 8750

20 10875

30 9400

Functions

Description

SQL Count function

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets on the number of rows or non NULL column values.

SQL Sum function

The SQL AGGREGATE SUM() function returns the sum of all selected column.

SQL Avg function

The SQL AVG function calculates the average value of a column of numeric type. It returns the average of all non NULL values

SQL Max function

The aggregate function SQL MAX() is used to find the maximum value or highest value of a certain column or expression. This function is useful to determine the largest of all selected values of a column.

SQL Min function

The aggregate function SQL MIN() is used to find the minimum value or lowest value of a column or expression. This function is useful to determine the smallest of all selected values of a column.

Date Functions in SQL:

ADD_MONTHS

LAST_DAY

MONTHS_BETWEEN:

NEXT_DAY:

SYSDATE:

ADD_MONTHS:Adds the specified number of months to a date.

LAST_DAY:Returns the last day in the month of the specified date.

MONTHS_ BETWEEN:Calculates the number of months between two dates.

NEW_TIME:Returns the date/time value, with the time shifted as requested by the specified time zones.

NEXT_DAY:Returns the date of the first weekday specified that is later than the date.

SYSDATE:Returns the current date and time in the Oracle Server.

Conversion Functions:

Conversion functions convert a value from one datatype to another. Generally, the form of the function name follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype. There are 5 conversion functions.

i.CAST

ii.TO_ CHAR

iii.TO _DATE

iv.CONVERT

v.TO _NUMBER

i.CAST:

Converts data from one type to another type.

Syntax:

SELECT CAST ( <source_operand > AS <data_type > ) FROM DUAL;

ii.TO_ CHAR:

Converts a date or number to a value of the VARCHAR2 datatype, using the optional format fmt.

Syntax for Dates:

TO_CHAR(d [, fmt])

Syntax for Numbers:

TO_CHAR(n [, fmt])

iii.TO _DATE:

The function takes character values as input and returns formatted date equivalent of the same. The TO_DATE function allows users to enter a date in any format, and then it converts the entry into the default format .

Syntax:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Example:

SELECT TO_DATE('January 26, 1996, 12:38 A.M.', 'Month dd YYYY HH:MI A.M.') FROM DUAL;

Returns the following result:

TO_CHAR(TO_DATE('JANUARY26’)

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

1996-01-26 12:38:00

iv.CONVERT:

Converts a character string from one character set to another.

The value_exp argument is the value to be converted.

The data_type argument is the name of the character set to which char is converted.

Syntax:

{ fn CONVERT(value_exp, data_type) }

Example

SELECT {fn CONVERT('Groß', 'US7ASCII') }

"Conversion" FROM DUAL;

Returns the following result:

conversi

--------

Groß

v.TO _NUMBER:

The TO_NUMBER function converts a character value to a numeric datatype. If the string being converted contains nonnumeric characters, the function returns an error.

Syntax

TO_NUMBER (string1, [format], [nls_parameter])

Example:

SELECT TO_NUMBER('121.23', '9G999D99')

FROM DUAL

TO_NUMBER('121.23','9G999D99')

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

121.23

OLAP FUNCTIONS :-

(I) It is also called as “Analytical function”

(II) This is two types

• Rank

• Dense_rank

(III) These two functions are used to calculate rank of a particular value from the set of values.

i.Rank:

Calculates the rank of a value in a group of values

Syntax :-

Rank( ) over (set of values)

ii.Dense_rank:

The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.

Syntax:

Dense_rank() over [set of values]

General Functions:

General functions are used to handle NULL values in database. The objective of the general NULL handling functions is to replace the NULL values with an alternate value. We shall briefly see through these functions below.

NVL:

The NVL function substitutes an alternate value for a NULL value.

Syntax:

NVL( Arg1, replace_with )

NVL2

As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns. NVL2 function can be used to substitute an alternate value for NULL as well as non NULL value.

Syntax:

NVL2( string1, value_if_NOT_null, value_if_null )

Ex:

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')

FROM employees;

NULLIF:

The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other null handling function, first argument can't be NULL.

Syntax:

NULLIF (expr1, expr2)

COALESCE:

COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.

Syntax:

COALESCE (expr1, expr2, ... expr_n )

GROUP BY clause:

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

GROUP BY clause

syntax:

SELECT column1,

SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

SELECT max(salary), dept

FROM employee

GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

The HAVING clause:

The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The syntax for the SQL HAVING Clause is:

SELECT expression1, expression2, ... expression_n,

aggregate_function (expression)

FROM tables

WHERE conditions

GROUP BY expression1, expression2, ... expression_n

HAVING condition;

Parameters or Arguments

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

condition is the condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

ORDER BY clause:

Description:

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

Syntax:

The syntax for the SQL ORDER BY clause is:

SELECT expressions

FROM tables

WHERE conditions

ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions are the columns or calculations that you wish to retrieve.

tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions are conditions that must be met for the records to be selected.

ASC is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC is optional. It sorts the result set in descending order by expression.