Oracle

Single-Row Functions in Oracle SQL with Examples11 min read

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.

SQL General Functions




NVL Function :The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.

Examples:

NVL Function Examples
NVL Function Examples

NVL2 Function: The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

Examples:

NVL2 Function
NVL2 Function Examples
NVL2 Function Examples

NULLIF Function: The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

Examples:

COALESCE Function: The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

Example:

DECODE Function: The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.

Example:

SQL Case Conversion Functions

LENGTH Function: The Oracle/PLSQL LENGTH function returns the length of the specified string.

CONCAT Function: The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.

Examples:

CONCAT Function
CONCAT Function
CONCAT Function Example
CONCAT Function

SUBSTR Function: The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

Examples:

SUBSTR Function
SUBSTR Function

INSTR Function: The Oracle/PLSQL INSTR function returns the location of a substring in a string.

LPAD Function: The Oracle/PLSQL LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null).

RPAD Function: The Oracle/PLSQL RPAD function pads the right-side of a string with a specific set of characters (when string1 is not null).

TRIM Function: The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the end of a string.

RTRIM Function: The Oracle/PLSQL RTRIM function removes all specified characters from the right-hand side of a string.

LTRIM Function: The Oracle/PLSQL LTRIM function removes all specified characters from the left-hand side of a string.

REPLACE Function: The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.

SQL Character Function

UPPER Function: The Oracle/PLSQL UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

LOWER Function: The Oracle/PLSQL LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

INITCAP Function: The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

SQL Number Functions

ROUND Function: The Oracle/PLSQL ROUND function returns a number rounded to a certain number of decimal places.

Örnekler

TRUNC Function: The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

MOD Function: The Oracle/PLSQL MOD function returns the remainder of m divided by n.

SQL Date Functions

SYSDATE Function: The Oracle/PLSQL SYSDATE function returns the current system date and time on your local database.

ADD_MONTHS Function:The Oracle/PLSQL ADD_MONTHS function returns a date with a specified number of months added.

MONTHS_BETWEEN Function: The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.

LAST_DAY Function: The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a date value.

NEXT_DAY Function: The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date.

ROUND Function: The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.

TRUNC Function: The Oracle/PLSQL TRUNC function returns a date truncated to a specific unit of measure.

Leave a Comment