SQL function: – SQL functions are used to enhance capacity of query. SQL functions are manly divided into three different categories. They are called number function, date function and character function.

Syntax: –

                Select Function_Name(Argument / Table_Field_Name) from Table_Name;

Number function: – The number functions are used with numeric type data. The argument to the function is number. There are number of numeric function available in SQL query. They are –

  1. ABS: – The ABS function find absolute value of an argument. The ABS function remove minus sign from negative value. For example, ABS(-9) return 9.
  2. CEIL: – The CEIL function return smallest integer greater than or equal to argument or in other word, CEIL function return next integer from a real number. For example CEIL(9.2) return 10.
  3. Floor: – The floor function return largest integer less than or equal to n. for equal to, floor(10.5) return 10.
  4. Mod: – The mod function return reminder of arguments. Here, reminder of m divided by n. for example, mode(17,5) return 2.
  5. Power: – The power function raised value of an argument. For example, power(3,3) return 27.
  6. Round: – Round function round off a real value up to specified decimal places. If decimal places is not specified, it round of in integer. For example, round(10.586,2) return 10.59 and round(10.586,0) return 11.
  7. Trunc: – The trunc function only truncate number of decimal places. For example, trunc(10.586,2) return 10.58 and trunc(10.586,0) return 10.
  8. Sign: – The sign function return sign value of an argument and it test the value of argument is positive, negative or neutral. If value of argument greater than zero, function return 1, if value of argument less than zero function return -1 and if value of argument equal to zero function return 0.
  9. SQRT: – The SQRT function return square root of a positive number. If value of an argument is negative or less than zero, function return an error message. For example, SQRT(9) return 3.

Date function: – The date function is used to change format of date value. There are five date function available in a SQL. The date functions are –

  1. Add_months: – This function used to add number of months in specified date value. For example, add_months(’17-May-99’,2) return 17-Jul-99.
  2. Last_day: – This function return last day of specified month. For example, last_day(’17-May-99’) return 31-May-99.
  3. Months_between: – This function return number of months between two specified date. For example, months_between(’17-May-99’,’19-Jul-99’) return 2.
  4. Next_day: – This function return next day of specified date. For example, next_day(’13-Apr-17’,’Wed’) return 19-Apr-17.
  5. Trunc: – This function truncates specified date and time. For example, trunc(’17-May-99’) return Null.

Character function: – A character functions are manly operated on character type data. There are number of function available in this category. They are –

  1. ASCII: – This function return ASCII code of first character. For example, ASCII(‘ABC’) return 65 because ASCII code of character A is 65.
  2. Chr: – This function return character of given ASCII code. For example, chr(65) return A.
  3. INITCAP: – This function return first letter as upper case letter and remaining character are unchanged. An example, INITCAP(‘suresh’) return Suresh.
  4. INSTR: – The INSTR function find a substring position from given string. For example, INSTR(‘abcabdebd’,’bd’,1,1) return 5 because substring bd find at fifth position.
  5. Length: – The length function find length of given string. For example, length (‘ABC’) return 3.
  6. Lower: – The lower function convert all alphabetic character from uppercase letter to lower case letter if alphabets is already in lower case letter, it cannot be change by the lower function. For example, lower(‘SURESH’) return suresh.
  7. Upper: – The upper function is just opposite to the lower function and it convert all lower-case letters to upper case letters. For example, upper(‘suresh’) return SURESH.
  8. LPAD: – The LPAD function left padded to length n with a sequence of characters. For example, LPAD(‘Nagpur ’,12,’City ’) return City Nagpur.
  9. RPAD: – The RPAD function right padded to length n with a sequence of characters. For example, RPAD(‘Nagpur ’,12,’City ’) return Nagpur City.
  10. Ltrim: – The ltrim function remove extra spaces from left side of a string. For example, ltrim(‘ ABC’) return ABC.
  11. Rtrim: – The rtrim function is just opposite of ltrim function. It removes number blank spaced from the right side. For example, rtrim(‘ABC ‘) return ABC.
  12. Soundex: – This function return true or false according to pronunciation of two string. For example, Soundex(‘Sun’,’Son’) return true because pronunciation of both words is same.
  13. Translate: – The translate function replace second sub string with main sub string according to first substring for example, translate(‘Mumbai’,’Mu’,’Ac’) return Acmbai.
Advertisements