Single row functions can be character functions, numeric functions, date functions, and conversion functions. Note that these functions are used to manipulate data items. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause.
General functions – Usually contains NULL handling functions. The functions under the category are NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.
Case Conversion functions – Accepts character input and returns a character value. Functions under the category are UPPER, LOWER and INITCAP.
UPPER function converts a string to upper case.
LOWER function converts a string to lower case.
INITCAP function converts only the initial alphabets of a string to upper case.
Character functions – Accepts character input and returns number or character value. Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE.
CONCAT function concatenates two string values.
LENGTH function returns the length of the input string.
SUBSTR function returns a portion of a string from a given start point to an end point.
INSTR function returns numeric position of a character or a string in a given string.
LPAD and RPAD functions pad the given string upto a specific length with a given character.
TRIM function trims the string input from the start or end.
REPLACE function replaces characters from the input string with a given character.
Date functions – Date arithmetic operations return date or numeric values. Functions under the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC.
MONTHS_BETWEEN function returns the count of months between the two dates.
ADD_MONTHS function add ‘n’ number of months to an input date.
NEXT_DAY function returns the next day of the date specified.
LAST_DAY function returns last day of the month of the input date.
ROUND and TRUNC functions are used to round and truncates the date value.
Number functions – Accepts numeric input and returns numeric values. Functions under the category are ROUND, TRUNC, and MOD.
ROUND and TRUNC functions are used to round and truncate the number value.
MOD is used to return the remainder of the division operation between two numbers.
Posted from: https://www.tutorialspoint.com/sql_certificate/using_single_row_functions.htm
Which three statements are true about single-row functions? (Choose three.)
- A. They can be used only in the WHERE clause of a SELECT statement
- B. The argument can be a column name, variable, literal or an expression
- C. The data type returned can be different from the data type of the argument
- D. They can be nested to any level
- E. They can accept only one argument
- F. They return a single result row per table