The SQL support various function that easily used to manipulate data. There are following three types of function.
- ABS (N) :- The function ABS is returns the absolute value of the column. Syntax:
SELECT ABS (-20) FROM DUAL;
- CEIL (N) :- The function CEIL is finds the smallest integer greater then or equal to N. N is a column name. Syntax:
SELECT CEIL (931-43), CEIL (BASIC) FROM EMPLOYEE;
- FLOOR (N) :- The function FLOOR is finds the largest integer less then or equal to N. Syntax:
SELECT FLOOR (432-343), FLOOR (BASIC) EMPLOYEE;
- MOD (M,N) :- The function MOD (M,N) is returns the remainder of M divided by N. If N=0; the function returns M. Syntax:
SELECT MOD (70,4) FROM DUAL;
- POWER (M,N) :- The function POWER (M,N) is returns the M to the power N. N is an integer. Syntax:
SELECT BASIC, POWER (BASIC) FROM EMPLOYEE;
- CONCAT (string 1, string 2) :- The function CONCAT (S 1,S 2) is returns string 1 appended by string 2. Syntax:
SELECT CONCAT (‘care’,’cars’) FROM DUAL;
- INITCAP (string) :- The function INITCAP (S) is capitalized the first character of each word in the string.
- LOWER (string) :- The function LOWER (S) is converts all character in the string to lower letters.
- UPPER (string) :- The function UPPER (S) is converts all character in the string to uppercase letters.
- SOUNDDEX (string) :- The function SOUNDDEX is returns a phonetic representation of each word.
- SUBSTR (string M,N) :- The function SUBSTR (string M,N) is returns the sub string. N character long from the storing starting from position M. Syntax:
SELECT SUBSTR (‘I will kill you’,8.u) FROM DUAL;
- SYSDATE :- The function SYSDATE is used to retrieve the current date and time to types DATE. Syntax:
SELECT SYSDATE FROM DUAL;
- ADD-MONTHS (D,N) :- This function adds N months to or from a date D. The result is returned as DATE type. Syntax:
SELECT DOJ,ADD main (DOJ.3). add-month (DOJ,-3) FROM EMPLOYEE;
- MONTHS-BETWEEN (D 1,D 2) :- This function is returns the number of months between two date D 1 and D 2. Syntax:
SELECT EMPLOYEE-NAME FLOOR (months-between (SYSDATE,DOJ)) FROM EMPLOYEE;
- TO-CHAR (D,’DAY’) :- This function is converts the date D to character formats. Syntax:
SELECT SYSDATE TO-CHAT (STSDATE,’DAY’) FROM DUAL;