Unit 3 :- Single-Row Functions

Functions are very powerful feature of SQL and 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 types.

SQL functions sometimes take arguments and always return a value.

There are Two distinct types of functions:

• Single-row functions.
• Multiple-row functions.

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types single-row functions. This section covers the following ones:

• Character
• Case-manipulation functions
• LOWER
• UPPER
• INITCAP
• Character-manipulation functions
• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD   |   RPAD
• TRIM
• REPLACE
• Number
• ROUND
• TRUNC
• MOD
• Date
• MONTHS_BETWEEN
• ADD_MONTHS
• NEXT_DAY
• LAST_DAY
• ROUND
• TRUNC
• Conversion
• Implicit data type conversion
• VARCHAR2 OR CHAR -----   TO -----   NUMBER
• VARCHAR2 OR CHAR -----   TO -----   DATE
• NUMBER                       -----    TO -----  VARCHAR2
• DATE                              -----    TO ----    VARCHAR2
• Explicit data type conversion
• TO_CHAR
• TO_DATE
• TO_NUMBER
• General
• NVL
• NVL2
• NULLIF
• COALESCE
• CASE
• DECODE

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in next Unit.