Introduction to SQL Single Row Functions 19012022 Slide

Introduction to SQL Single Row Functions 19/01/2022 Slide : 1

Overview of Functions in SQL 19/01/2022 Slide : 2 Use functions to Perform calculations on data Modify individual data items Manipulate output for groups of rows Alter date formats for display Convert column datatypes

Single row functions Two Types of SQL Functions Character Number Date Conversion Multiple row functions Group 19/01/2022 Slide : 3

Manipulate data items Single Row Functions: Syntax Accept arguments and return one value Act on each row returned Return one result per row Modify the datatype Can be nested 19/01/2022 Slide : 4

Character Functions 19/01/2022 Slide : 5 LOWER convert to lowercase UPPER convert to uppercase INITCAP convert to capitalization CONCAT concatenate values SUBSTR return substring LENGTH return number of characters NVL converts a null value

Number Functions 19/01/2022 Slide : 6 ROUND rounds value TRUNC truncates value MOD returns remainder of division

Arithmetic Operators with Dates 19/01/2022 Slide : 7 Add or substract a number to or from a date for a resultant date value Substract two dates to find the number of days Add hours to a date by dividing the number of hours by 24

Date Functions MONTHS_BETWEEN number of month ADD_MONTHS add calendar NEXT_DAY next day LAST_DAY last of month ROUND to value TRUNC truncate to value 19/01/2022 Slide : 8 round

TO_CHAR converts a number or date string to a character string Conversion Functions TO_NUMBER converts a character string containing digits to a number TO_DATE converts a character string of a date to a date value Conversion functions can use a format model composed of many elements 19/01/2022 Slide : 9

Nesting Functions 19/01/2022 Slide : 10 First evaluate the inner function Then evaluates the outer function And goes on like that