SQL Functions: Powerful Tools for Data Manipulation and Formatting

1. Lecture 5 SQL FUNCTIONS

2. Functions are a very powerful feature of SQL. They can be used to do the following: • Perform calculations on data • Modify individual data items • Manipulate output for groups of rows • Format dates and numbers for display • Convert column data typesSQL functions sometimes take arguments and always return a value.

3. Types of SQL functions There are two types of functions: • Single-row functions • Multiple-row functions

4. Single Row Functions These functions operate on single rows only and return one result per row. There are different types of single-row functions. • Character • Number • Date • Conversion • General

5. Multiple-Row Functions • Functions can manipulate groups of rows to give one result per group of rows. These functions are also known as group functions. Example: • Avg(),count(),max(),min(),sum()

6. Single Row Function Description • Manipulate data items • Accept arguments and return one value • Act on each row that is returned • Return one result per row • May modify the data type

7. Type of Argument in the Function • User-supplied constant • Variable value • Column name • Expression. Single row functions can be used in SELECT,WHERE,ORDERBY clauses.

8. Syntax • function_name is the name of the function, arg1, arg2 is any argument to be used by the function. • This can berepresented by a column name or expression. • Function_name(arg1,arg2…)

9. Type of Single Row Functions  Character functions: Αccept character input and can return both character and number values • Number functions: Accept numeric input and return numeric values • Date functions: Operate on values of the DATE data type. • Conversion functions: Convert a value from one data type to another • General functions:- NVL,NVL2,NULLIF,CASE,DECODE

10. Character Function Two types: Case conversion function. Character manipulation functions.

11. Case Conversion Function LOWER, UPPER • LOWER Converts alpha character values to lowercase SELECT LOWER(ENAME) FROM employee

12. UPPER: Converts alpha character values to uppercase. SELECT UPPER(ENAME) FROM employee

13. Character Manipulation Function CONCAT,SUBSTR.LENGTH,TRIM • CONCAT: Joins values together (You are limited to using two parameters with CONCAT.) SELECT CONCAT(ENAME,EMPLNO) FROM employee • SUBSTR: Extracts a string of determined length SELECT SUBSTR(ENAME,1,5) FROM employee

14. LENGTH: Shows the length of a string as a numeric value SELECT length(ENAME) FROM employee • INSTR: Finds numeric position of a named character SELECT INSTR(ENAME,'A') FROM employee

15. Replace • Replace lets you substitute one string for another as well as to remove character strings. • Select replace(‘JACK’, ‘J’, ‘BL’); Output: BLACK Reverse: Reverse characters in a string Select reverse(‘abcd’); Output: dcba

16. MID() • Used to extract character from the text field. MID(name, 1 ,4)

17. Number Function •  ROUND: Rounds value to a specified decimal Round(45.436,2) Result: 45.44 • TRUNC: Truncates value to a specified decimal TRUNCATE(45.436,2) Result: 45.43 • MOD: Returns remainder of division MOD(1600,300) Result: 100

18. Controlling for number precision • Ceil and floor functions are used to round either up or down to the closest integer. • Select ceil(72.445) 73 • floor(72.445) 72

19. DATE FUNCTIONS • Used to perform date calculations or format date values. • MYSQL displays date in this format'YYYY-MM-DD'  • Curdate() for retrieving current date. • CURTIME() for retrieving current time • NOW() for retrieving time and date.