**SQL Scalar Functions** are the user-defined functions that allow you to perform different calculations on data values. These functions operate on single rows only and produce one result per row. There are different types of Scalar Functions. They are as follows:

Except for aggregate functions, all the scalar function uses the table called **automatically created by Oracle Database with the data dictionary**. This table**SYS** schema but it is available by the '**DUAL**' name to all users.

**The**

**Let us have a look on the DUAL **

`SELECT * FROM DUAL;`

**Numeric functions** manipulate the **numeric** input values. The return type for numeric functions is **NUMBER**.

Some of the most commonly used **NUMERIC** functions are as follows.

- TRUNC
- ROUND
- CEIL
- POWER
- SQRT

Now let us understand the above

This function is used to **truncate** or **reduce** the decimal place.

**Syntax of TRUNC() **

`SELECT TRUNC(n1 [, n2 ]) FROM DUAL;`

**n1**: A number to be truncated up to n2 decimal places.**n2**: A number indicating up to how many decimal places, n1 is to be truncated.

**Example of TRUNC() **

`SELECT TRUNC(10.375,1) FROM DUAL;`

In the above query, the n1 is truncated up to the first decimal.

**Example of the TRUNC()**

`SELECT TRUNC(12.96) FROM DUAL;`

In the above query, the **n2** is omitted. So, **n1** is truncated to **0** places of its decimal place.

**Example of the TRUNC()**

`SELECT TRUNC(12.96,-2.7) FROM DUAL;`

In the above query, the **n1** is omitted because the **n2** is negative. So the result is **0. **

**Example of the TRUNC() function where n1 and n2 are negative : **

`SELECT TRUNC(-12.96,-2.7) FROM DUAL;`

In the below query, the **n1** and **n2** are in the negative integer. If the **n2** is negative, the output is **0**.

The **n(number)** rounded value to an integer place to the right of its decimal point. If you neglect the integer before the decimal point, then **n** is rounded to **0** places.

**Syntax of the ROUND() **

`SELECT ROUND( number [, decimal_places] ) FROM DUAL;`

**Example of the ROUND() **

`SELECT ROUND(10.4733,1) FROM dual;`

In the above query, the statement will round the given number **10.4733** up to **1** decimal place.

**Example of the ROUND() **

`SELECT ROUND(10.4733,7) FROM dual;`

In the above query, the value of the decimals doest change when the **n2** value is higher than the decimal place of **n1**.

**Example of the ROUND() **

`SELECT ROUND(10.7) FROM dual;`

In the above example, the output is retrieved when the decimal value is **.5** or more than **.5**.

**Example of the ROUND() **

`SELECT ROUND(10.4) FROM dual;`

In the above example, the output is retrieved when the decimal value is

The

The CEIL() function always returns in the whole number.

**Syntax of CEIL() **

`SELECT CEIL(n) FROM DUAL;`

where **n** is the value of the number.

**Example of CEIL() **

`SELECT CEIL (10.0001) FROM dual;`

In the above query, the statement returns 11 which is the immediate value of the specified number

`SELECT CEIL (-10.0001) FROM dual;`

In the above query, the statement returns **-10** which doesn't return the decimal value **(-10.0001)** in the argument.

**Syntax of POWER() **

`SELECT POWER(n,p) FROM DUAL;`

**n**: An integer number.**p**: The value of the power value.

**Example of POWER() **

`SELECT POWER(5,2) FROM DUAL;`

This function gives the square root of the given number.

**Syntax of SQRT()**

`SELECT SQRT(n) FROM DUAL;`

where **n** is the number that to be square rooted.

**Example of SQRT()**

`SELECT SQRT(9) FROM DUAL;`

**Conversions functions** are used to convert from one data type to another type. The **string value** to a **number** in order to the specified format.

Syntax of

`TO_NUMBER( input_value, expr_format)`

Some of the examples of the

**Example-1:**`SELECT TO_NUMBER('175.12') FROM DUAL;`

The above query converts a simple string to a number value.

**Example 2:**`SELECT TO_NUMBER('1234', '99999') FROM DUAL;`

The above query converts a number that has no decimal places in it.

**Example 3:**`SELECT TO_NUMBER('$123.44', 'L999.99') FROM DUAL;`

The above query converts the number from the dollar sign as a currency figure.