Basics Function of SQL

The SQL support various function that easily used to manipulate data. There are following three types of function.

Arithmetic 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;

Character function

  • 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;

Date function

  • 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;