Built in Functions in SQL

Functions:

•      Function can return an only single row or a table.
•   It is a set of SQL statements that only accept input parameters, perform Task and then return the result.

Two types of Functions:

1.    Built in Functions

2.    User defined functions

Built in Functions:

·       These functions are defined by the SQL SERVER for different purpose.

Types:

1.    Scalar OR single row function

2.    Aggregate functions or group function

Before going to the function we just know the oracle Created table DUAL:

·       DUAL is a table automatically created by oracle along with the data dictionary. DUAL is the schema of the users SYS, but it is accessible by the all users and the name of dual.

·       It has one DUMMY column defined to be varchar2(1) and contains one row with a value ‘X’

·       Dual table is useful for computing constant expression with the SELECT statement.

Desc DUAL;

Select * from dual;

Scalar functions:

 Function Description Example Output Abs(n) It returns absolute values Select abs(-100) from dual; 100 Floor(n) It returns largest integer equal to or less than n Select floor(32.75) from dual; 32 Ceil(n) It returns largest integer equal to or greater than n Select ceil(32.75) from dual; 33 Exp(n) E to the power of n Select exp(4) from dual; 54.598150033144236 Ln(n) It returns log value of n Select ln(2) from dual; 0.693147185599453 Mod(m,n) It returns remainder Select mod(10,3) from dual; 1 Power(m,n) It returns m to the power of n Select power(4,2) from dual; 16 Round(n,[m]) It returns round of the particular value Select round(135.375, 2) from dual; 135.38 Sign(n) It returns a value of -1 for negative expressions and +1 for positive expressions Select sign(-20.5) from dual; -1 Sqrt(n) Square root of n Select sqrt(4) from dual; 2 Trunc(n,[m]) It returns n truncated to m decimal places Select trunc(10.37, 3)from dual; 10.370

Character functions:

 Function Description Example ASCII(char) It returns ASCII value of the character Select Ascii(Ename) from emp; INSTR( string1,string2) It returns position of a substring in a string Select Instr(‘Sql’,’q’) Length(string) It returns the length of the string in bytes Select Length(‘date’) from dual; Lower(string) It converts string to lower case Select Lower(‘SQL’) from dual; Upper(string) It converts string to upper  case Select Upper(‘sql’) from dual; Lpad(string,length,lpad_string) Left pads a string with another ,to a certain length Select Lpad(ename,20,’emp’) from emp; Rpad(string,length,rpad_string) Right pads a string with another ,to a certain length Select Rpad(ename,20,’emp’) from emp; Ltrim(string) Removes leading spaces from a string. Select Ltrim(‘   SQL‘) from dual; Rtrim(string) Removes trailing spaces from a string. Select Rtrim(‘SQL   ‘) from dual; Replace(string,from_string, new_string) Replaces all occurrences of the sub string with in a string, with a new string Select Replace(‘xyz_yz’,’x’,’z’)From dual; Substr(string, start, length) It extract a sub string from a string and starting with any position Select Substr(ename,2,6)From emp;