Different Sql Functions With Examples
Different Sql Functions With Examples: Functions are used to perform actions on a single value or a table. Functions accept values, take action on it and then give desired results. Functions cannot be used to insert, delete or update the values. To perform operations on data, sql server gives many functions ,SQL supports distinct types of functions and these sql server functions are as follows:
Also See: What is Sql, its applications, advantages and disadvantages
Different Sql Functions With Examples
- Numeric functions
- String functions
- Aggregate functions
- Date functions
- Conversion functions
Also Read: Different SQL Server Constrains with Examples
-
Numeric functions:
Abs ( ): this function is used to convert a negative value to positive value.
Syntax: abs(No.)
Example:
Sql >select abs(100-120) from dual;
Output: 20
Sqrt ( ): this function returns the square root of a given number.
Syntax _ SQRT (No.)
Example:
SQL> select SQRT (81) from dual;
Output: 9
Mod ( ): this function returns the modulus.
Syntax: mod (dividend, divisor)
Example:
SQL> select mod (15,2) from dual,
Output: 1
Power ( ): this function returns the power of given number as per the raised number specified.
Syntax: power (number, raised number)
Example:
SQL> select power (4, 3) from dual;
Output: 64
Round ( ): this function is used to round off the required number of decimals.
Syntax – round (No., No. of decimals)
Example:
SQL> select round (23.6789, 2) from dual;
Output: 23.68
Also Read: Explain Data Manipulation Language (DCL) with examples
-
String functions:
Upper ( ): this function converts a string into upper case.
Syntax: upper (‘string’)
Example:
Sql> select upper (‘hello’) from dual;
Output: HELLO
Lower ( ): this function converts a string into lower case.
Syntax: lower (‘string’)
Example:
Sql> select upper (‘HELLO’) from dual;
Output: hello
Initcap ( ): this function converts a string into initial caps.
Syntax: initcap (‘string’)
Example:
Sql> select initcap (‘computer education’) from dual;
Output: Computer Education
Itrim ( ): this function is availed to remove the unnecessary spaces or characters available to a string.
Syntax: Itrim (‘string’, ‘unnecessary char’)
Example:
Sql> select Itrim (‘xyxyhello’, ‘xy’)
Output: hello
Rtrim ( ): this function is used to remove the unnecessary characters or spaces available on the right side of a string.
Syntax: rtrim (‘string’, ‘unnecessary char’)
Example:
Sql> select rtrim (‘computerxyzxy’, ‘xy’) from dual;
Output: computerxyz
Also Read: Explain Transaction Control Language With Examples
-
Aggregate functions:
Aggregate functions return single values by performing action of a group of values. consider the following example of table Emp to simply understand its working
Table Emp
| Emp_id | Name | Salary | Age |
| 350 | Mark | 55000 | 28 |
| 351 | Steve | 60000 | 30 |
| 352 | Roser | 60000 | 31 |
| 353 | Joseph | 75000 | 34 |
| 354 | Avlin | 90000 | 32 |
Sum ( ): this function calculates a sum of a group of values.
Syntax: sum(col _name)
Example:
Select sum(salary) from Emp;
Output:
340000
Average ( ): this function returns the average of group of values.
Syntax: average(col _name)
Example:
Select avg(salary) from Emp;
Output:
68000
Max ( ): this function returns the highest value from group of values.
Syntax: max(col _name)
Example:
Select max(salary) from Emp;
Output:
90000
Min ( ): this function returns the least value from group of values.
Syntax: min(col _name)
Example:
Select min(salary) from Emp;
Output
55000
Count ( ): this function counts the number of values in a group.
Syntax: count(col _name)
Example:
Select count(name) from Emp where salary = 60000;
Output:
2
-
Date functions:
Sysdate: this function returns the system date in the format of dd-mm-yy.
Syntax: sql> select sysdate from dual;
Last_ day ( ): this function returns the last date of a given date.
Syntax: last _day(date)
Next _ day ( ): this function returns the next date of a given date.
Syntax: next _day (date, ‘dy’)
Add _ months ( ): this function is availed to add or subtract the number of months to a given date.
Syntax: add _months(date, no. of months)
Months _ between ( ): this function returns the number of months present between two given dates.
Syntax: months _between(date1, date2)
-
Conversion functions:
To _ char ( ): this function converts a string or value into characters or in a specified format.
Syntax: to_ char(value, ‘format’);
To _ date ( ): this function is used to convert a string value into date format.
Syntax: to _date(‘value’, ‘format’);
To _ number ( ): this function converts a string value into numeric.
Syntax: to_ number(‘value’);
So it was all about Different Sql Functions With Examples. if you have any doubt then please comment below.
Leave a Reply