Oracle Scalar Functions help you perform various calculations on database columns. A scalar function operates on a single row only and provide single result per row.
Basic and important Scalar functions are illustrated and demonstrated below:
- String functions
- Numeric functions
- Date functions
- Conversion functions
- Functions that work around NULL values
Let us look at illustrations of each of these Scalar functions. You can click through the links to understand it with better examples.
String functions operate on columns that hold character values. Character values are specified by single-quotes ‘ ‘ in Oracle.
String functions help you do ultimate string manipulations on database columns. Some of them are illustrated below.
- Concatenating two strings
- Identifying character/string position
- Length of a string
- Trimming/truncating spaces
- Retrieving a part of a string
- Replacing character/strings
- Reversing string value
- Converting to Lower case
- Converting to Upper case
- Converting first character to Upper case
Numeric functions operate on columns that are of Number type. Below are some of the Numeric functions supported by Oracle.
- Truncating decimal points in a column
- Ceiling a number to a higher value
- Rounding off a number to remove decimals
Oracle Date functions operate on operate on columns that are of type Date.
Formatting and Converting date type columns are very essential for an SQL developer in any industry. Below are the various Date functions in Oracle.
- Adding Months / Days to a date
- Fetch Day / Month / Year from a date
- Identify Last / First of a Month or Year
- Counting months between Dates
- Identifying current date
Oracle Conversion functions are special functions especially used to convert a data from one type to another type.
There are times when an SQL query is expecting a data type but it got another. In those cases data type conversion is very essential. Some of them are illustrated below.
- Converting a column to Character type
- Converting a column to Date type
- Converting a column to Number
NULL specific functions
Handling NULL values in Oracle is very important to maintain the validity of the data in a database column.
Oracle NVL function helps to replace a NULL value with another value in the specified column.
NVL( column_name , value_to_replace )
Assume, the Phone Numbers in Employees table contain NULL and your client wants to view the phone numbers and you can’t simply display a NULL value. You end up displaying a proper message by replacing the NULL value. Everyone is happy, isn’t ?
SELECT NVL( PHONE_NUMBER , 'Not Available' ) FROM EMPLOYEES;
Thank you for reading.
Please visit https://www.sql-scripts.com for anything that is related SQL Queries, Scripts and How to stuffs.
Tags: Oracle Scalar functions| SQL Scalar functions | Scalar functions in SQL | Oracle SQL Basics