Number Functions in SQL

• ROUND : Rounds values to specified decimal.

                   ROUND(45.878,2)       ------------------->   45.88

• TRUNC  :  Truncates value to specified decimal.

                   TRUNC(45.878,2)       -------------------->   45.87

• MOD : Returns remainder of division.

                   MOD(1600,300)          -------------------->  100

Example:

ROUND

The Round Function rounds the column, expression, or value to n decimal place.

If the second argument is 0 or is missing, the value is round to zero decimal place.

If the second argument is 2, the value is rounded to two decimal places.

If the second argument is -2, the value is rounded to two decimal places to the left.

TRUNC

The TRUNC function truncates the column, expression, or value to n decimal places.

If the second argument is 0 or is missing, the value is truncated to zero decimal places.

If the second argument is 2, the value is truncated to two decimal places.

If the second argument is -2, the value is truncated to two decimal places to the left.

Character Functions in SQL

• Case-manipulation functions
• LOWER   :- Converts alpha character values to lowercase.
• UPPER   :-  Converts alpha character values to uppercase.
• INITCAP  :-  Converts alpha character values to uppercase for the first letter of               each word.
• Character-manipulation functions
• CONCAT  :-  Concatenates the first character values to second character value.
• SUBSTR  :-  Return specified characters from character value.
• LENGTH  :-  Returns the number of character in the expression.
• INSTR  :-  Returns the numeric position of a named string.
• LPAD  :-  Pads the character value right justified to a total width of n.
• RPAD  :- Pads the character value left justified to a total width of n.
• TRIM  :-  Enables you to trim heading or trailing characters from a character string.
• REPLACE  :-  Searches a text expression for a character string and if found, replaces it with a specified replacement string.

Example:

LOWER

UPPER & INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD & RPAD

TRIM

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.

SORTING in SQL

The default sort order is ascending:

• Numeric values are displayed with the lowest value first e.g. 1-10
• Date values are displayed with the earliest value first e.g. 01-JAN-12 before 12-FEB-13
• Character values are displayed in alphabetical order e.g. A first Z last
• Null values are displayed last for ascending sequence and first for descending sequence.

Sorting in DESCENDING Order

Sorting by Column Alias

We can use a column alias in the ORDER BY clause. The below example sorts the data by Annual Salary.

Example:

Sorting By Multiple Columns:

We can sort query by more than one column. The sort limit is the number of columns in the given table.

Example: