SQL general functions are built-in tools provided by SQL databases to perform a variety of operations on data. These functions are crucial for manipulating, analyzing, and transforming data efficiently.
In this article, We will learn about the what are the SQL General Functions along with the examples and so on.
SQL General Functions
- SQL general functions are built-in functions provided by SQL databases to perform various operations on data.
- These functions are categorized into several types based on their functionality.
Single Row Functions
1. NVL Function in SQL
- In SQL, NVL() converts a null value to an actual value.
- Data types that can be used are date, character and number.
- The data type must match with each other i.e. expr1 and expr2 must be the same data type.
Syntax –
NVL (expr1, expr2)
expr1
is the source value or expression that may contain a null.
expr2
is the target value for converting the null. Example –
SELECT salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) annual_salary FROM employees;
Output
:
2. NVL2(expr1, expr2, expr3)
- The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression.
- If the first expression is null, then the third expression is returned i.e. If expr1 is not null, NVL2 returns expr2.
- If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
Syntax –
NVL2 (expr1, expr2, expr3)
expr1
is the source value or expression that may contain null
expr2
is the value returned if expr1 is not null
expr3
is the value returned if expr1 is null
Example –
SELECT last_name, salary, commission_pct, NVL2(commission_pct, ’SAL+COMM’, ’SAL’) income FROM employees;
Output
:
3. DECODE()
- Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement. The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages.
- The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.
- If the default value is omitted, a null value is returned where a search value does not match any of the result values.
Syntax –
DECODE(col|expression, search1, result1 [, search2, result2,...,][, default])
Example
–
SELECT last_name, job_id, salary, DECODE(job_id, ’IT_PROG’, 1.10*salary, ’ST_CLERK’, 1.15*salary, ’SA_REP’, 1.20*salary,salary) REVISED_SALARY FROM employees;
Output
:
4. COALESCE()
- The COALESCE() function examines the first expression, if the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
- The advantage of the COALESCE() function over the NVL() function is that the COALESCE function can take multiple alternate values.
- In simple words COALESCE() function returns the first non-null expression in the list.
Syntax –
COALESCE (expr_1, expr_2, ... expr_n)
Examples
–
SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;
Output
:
5. NULLIF()
- The NULLIF function compares two expressions. If they are equal, the function returns null.
- If they are not equal, the function returns the first expression. You cannot specify the literal NULL for first expression.
Syntax –
NULLIF (expr_1, expr_2)
Examples
–
SELECT LENGTH(first_name) "expr1", LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name),LENGTH(last_name)) result FROM employees;
Output
:
6. LNNVL()
- LNNVL evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query.
- It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE.
Syntax –
LNNVL( condition(s) )
Examples
–
SELECT COUNT(*) FROM employees WHERE commission_pct < .2;
Output
:
Now the above examples does not considered those employees who have no commission at all. To include them as well we use LNNVL()
SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);
Output:
7. NANVL()
- The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs the Database to return an alternative value n2 if the input value n1 is NaN (not a number).
- If n1 is not NaN, then database returns n1. This function is useful for mapping NaN values to NULL.
Syntax –
NANVL( n1 , n2 )
Consider the following table named nanvl_demo :
Example
–
SELECT bin_float, NANVL(bin_float,0) FROM nanvl_demo;
Output
:
Conclusion
SQL general functions are essential for streamlining data operations and enhancing the flexibility of SQL queries. They provide powerful capabilities for handling null values, conditional logic, and data conversions, thus enabling more sophisticated data manipulation and analysis.
Similar Reads
PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part o
4 min read
SQL | Numeric Functions
SQL Numeric Functions are essential tools for performing mathematical and arithmetic operations on numeric data. These functions allow you to manipulate numbers, perform calculations, and aggregate data for reporting and analysis purposes. Understanding how to use SQL numeric functions is important
4 min read
SQL Server Group Functions
The group function in SQL Server provides a powerful tool for performing calculations on groups of rows, allowing you to group data based on specific criteria. This function is important when you want to analyze and summarize information from multiple records in a data structure. The basic group fun
3 min read
SQL Server LEN() Function
SQL SERVER LEN() function calculates the number of characters of an input string, excluding the trailing spaces. LEN() Function in SQL ServerThe LEN function in the SQL Server fetches the number of characters in a string. It counts the preceding spaces but not the trailing spaces. For eg, 'SQL SERVE
2 min read
Math Functions in PL/SQL
In PL/SQL, mathematical functions play a important role in performing calculations and manipulating numeric data. These functions allow us to execute a wide range of mathematical operations from basic arithmetic to complex computations within our PL/SQL code. In this article, we will learn about Mat
4 min read
SQL LAG() Function
The LAG() function in SQL is a powerful window function that allows you to retrieve the value of a column from the previous row in the result set. It is commonly used for tasks like calculating differences between rows, tracking trends, and comparing data within specific partitions. In this article,
4 min read
SQL MIN() Function
The MIN() function in SQL is a powerful tool that allows us to determine the smallest or lowest value from a specified column or expression. It is widely used in data analysis to extract minimum values for decision-making, reporting, and business insights. This function automatically excludes NULL v
8 min read
SQL MAX() Function
The MAX() function in SQL is a powerful aggregate function used to retrieve the maximum (highest) value from a specified column in a table. It is commonly employed for analyzing data to identify the largest numeric value, the latest date, or other maximum values in various datasets. The MAX() functi
4 min read
PL/SQL MAX() Function
The PL/SQL MAX() function is an essential aggregate function in Oracle databases, enabling users to efficiently determine the largest value in a dataset. Whether working with numerical data, dates, or strings, the MAX() function is flexible and widely applicable. In this article, we will provide a d
4 min read
PL/SQL MIN() Function
PL/SQL means Procedural Language / relational Structured Query Language, the extended language of Oracle. It is primarily used to manage and manipulate databases. One of the most frequently utilized SQL functions is the MIN() function. This powerful aggregate function is essential for finding the sm
6 min read