SQL Function

Scalar functions

Scalar functions operate against a single value, and return a single value based on the input value.

We group the scalar functions into three categories and discuss them one by one. The three categories are:

Numeric functions String Functions Conversion functions

About Dual

Before explaining the various Scalar functions, lets understand first, what a Dual is and its use. A Dual is a small worktable. It consists of one row and one column only and supports arithmetic calculations, date retrieval and it's formatting.

There are situations when arithmetic calculations are to be done such as 14*26, where no table is referenced as only numeric literals are used. However to display the output on screen, SQL has SELECT option which makes mentioning a table name mandatory in its FROM clause. In such cases we use Dual. It is a virtual/dummy table provided by Oracle to facilitate such calculations.

In our examples using Scalar functions, we will make use of Dual.

Numeric functions

Given below is the list of some Numeric Functions with their Descriptions:

We display the examples using the above functions and their respective output in the tabular form given below:

Notice in the above table, we have used 'dual' as table name.

String Functions

Given below is the list of some commonly used STRING FUNCTIONS with their Descriptions:

We display the examples using the above functions and their respective output in the tabular form given below:

We display the examples using the above functions and their respective output in the tabular form given below: