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;

 

 

 

 

Share: