Table of Contents
Introduction to Single Row Function in SQL
Single row function in SQL are the ones who work on a single row and return one output per row.
Single row function in SQL can be character, numeric, date, and conversion functions. these functions are used to modify data items. These functions need one or more input and operate on each row, thereby returning one output value for each row.
Types of Single row function In SQL
- Character Function
- General Function
- Case conversion Function
- Date Function
- Number Function
General functions
It usually contains NULL handling functions.
The types of General functions are
- NVL
- NVL2
- NULLIF
- COALESCE
- CASE
- DECODE.
Widget not in any sidebars
Case Conversion functions
Accepts character data and returns a character value.
Types of Case Conversion function
- UPPER
- LOWER
- INITCAP.
- The UPPER function converts a string to upper case.
- LOWER function converts a string to lower case.
- INITCAP function converts only the initial letters of a string to upper case.
Character functions – Accepts string input and return number or string value.
Types of character functions
- CONCAT
- LENGTH
- SUBSTR
- INSTR
- LPAD
- RPAD
- TRIM
- REPLACE.
- CONCAT function is used to concatenates two string values.
- LENGTH function returns the length of the input character.
- SUBSTR function returns a part of a string from a given start point to an endpoint.
- INSTR function returns the numeric position of a character or a string in a given order.
- LPAD and RPAD functions pad the given string up to a specific length with a given type.
- TRIM function trims the data input from the start or end.
- REPLACE function replaces characters from the input character.
Date functions – Date arithmetic operations return date or numeric values in SQL.
Widget not in any sidebars
The type of date function is
- MONTHS_BETWEEN,
- ADD_MONTHS,
- NEXT_DAY,
- LAST_DAY,
- ROUND,
- TRUNC.
- MONTHS_BETWEEN function returns the sum of months between the two dates which we input.
- ADD_MONTHS function adds ‘n’ number of months to a specifies date.
- NEXT_DAY function returns the next day of the data we input.
- LAST_DAY function returns the last day of the month of the date we input.
- ROUND and TRUNC functions are used to take rounded and truncates the date value.
- Number functions – Accepts numeric data and returns numeric values. Type of Number Function is ROUND, TRUNC, and MOD.
- ROUND and TRUNC functions are used to take rounded and truncate the number value.
- MOD is used to return the remainder of the division method between two numbers.
