- 71 Views
- Uploaded on
- Presentation posted in: General

Number Functions

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Number Functions

- single-row character functions
- character case-manipulation functions
- LOWER, UPPER, INITCAP

- character-manipulation functions
- CONCAT, SUBSTR,
- LENGTH, INSTR
- LPAD, RPAD
- TRIM
- REPLACE

- character case-manipulation functions

- single-row number functions
- ROUND, TRUNC, and MOD
- Distinguish between TRUNC and ROUND when they are applied to a numeric value
- State the implications for business when applying TRUNC and ROUND to numeric values

- One of the reasons we put our money in a bank is to take advantage of the interest it accumulates over time.
- Banks adjust the interest rate with various economic indicators such as inflation and the stock market.
- Typically, interest rates are expressed as a percent such as 3.45%.

- What if a bank decided to round the percentage rate to 3.5%? Would it be to your advantage?
- What if they decided to just drop the decimal values and calculate the interest at 3%, would you be happy then?
- Rounding and truncating numbers play an important part in business and in turn with the databases that support these businesses as they store and access numeric data.

- accept numeric input and return numeric values
- The three number functions are:
- ROUND
- TRUNC
- MOD

- Used to round numbers to a specified number of decimal places.
- ROUND can also be used to round numbers to the left of the decimal point.
- ROUND can also be used with dates.
- Syntax
- ROUND(column|expression, decimal places)
select round(2.55,1) from dual;

- ROUND(column|expression, decimal places)

- If the number of decimal places is a positive number, the number is rounded to that number of decimal places.
- If the number of decimal places is a negative number, numbers to the left of the decimal are rounded.
- if the number of decimal places is not specified or is zero, the number will round to no decimal places.

- For NUMBER values, the value n is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative).
- For BINARY_FLOAT and BINARY_DOUBLE values, the function rounds to the nearest even value. Please refer to the examples that follow.

- Used to terminate the column,expression, or value to a specified number of decimal places.
- TRUNC can also be used with dates.
- Syntax
- TRUNC(column|expression, decimal places)
- TRUNC (45.926, 2) yields 45.92

- TRUNC(column|expression, decimal places)
- if the TRUNC expression does not specify the number of decimal places or specifies a zero, the number is truncated to zero decimal places.
- TRUNC (45.926) yields 45

- For NUMBER and BINARY_FLOAT and BINARY_DOUBLE values, the rule is the same now.

- Used to return the remainder when one number is divided by another.
- For example, the MOD of 5 divided by 2 = 1.

- Returns n2 if n1 is 0.
- This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)

FLOOR(n) returns largest integer equal to or less than n.

- MOD can be used to determine whether a value is odd or even.
- If MOD(n,2) equals 0,n is even
- If MOD(n,2) equals 1,n is odd
SELECT last_name, salary

FROM f_staffs

WHERE MOD(salary, 2)=0;

- The above query will show the staffs whose salary is an even
number.

- The above query will show the staffs whose salary is an even

- Key terms used in this lesson include:
- Number functions
- MOD
- ROUND
- TRUNC

- Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query
- Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value
- State the implications for business when applying TRUNC and ROUND to numeric values