Oracle Single Row Functions Examples

Oracle Single Row Functions Examples

Oracle provides single row functions to manipulate the data values. The single row functions operate on single rows and return only one result per row. In general, the functions take one or more inputs as arguments and return a single value as output. The arguments can be a user-supplied constant, variable, column name and an expression.

The features of single row functions are:

• Act on each row returned in the query.
• Perform calculations on data.
• Modify the data items.
• Manipulate the output for groups of rows.
• Format numbers and dates.
• Converts column data types.
• Returns one result per row.
• Used in SELECT, WHERE and ORDER BY clauses.
• Single row functions can be nested.

The single row functions are categorized into

• Character Functions: Character functions accept character inputs and can return either character or number values as output.
• Number Functions: Number functions accepts numeric inputs and returns only numeric values as output.
• Date Functions: Date functions operate on date data type and returns a date value or numeric value.
• Conversions Functions: Converts from one data type to another data type.
• General Functions

Let see each function with an example:

Character Functions Example

1. LOWER

The Lower function converts the character values into lowercase letters.

SELECT lower('ORACLE') FROM DUAL;

2. UPPER

The Upper function converts the character values into uppercase letters.

SELECT upper('oracle') FROM DUAL;

3. INITCAP

The Initcap function coverts the first character of each word into uppercase and the remaining characters into lowercase.

SELECT initcap('LEARN ORACLE') FROM DUAL;

4. CONCAT

The Concat function coverts the first string with the second string.

SELECT concat('Oracle',' Backup) FROM DUAL;

5. SUBSTR

The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end are returned.

Syntax: substr(string [,m,n])

SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;

SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;

You can specify m value as negative. In this case the count starts from the end of the string.

SELECT substr('ORACLE BACKUP',-6) FROM DUAL;

6. LENGTH

The Length function is used to find the number of characters in a string.

SELECT length('Oracle Data Guard') FROM DUAL;

7. INSTR

The Instr function is used to find the position of a string in another string. Optionally you can provide position m to start searching for the string and the occurrence n of the string. By default m and n are 1 which means to start the search at the beginning of the search and the first occurrence.

Syntax: instr('Main String', 'substring', [m], [n])

SELECT instr('oralce apps','app') FROM DUAL;

SELECT instr('oralce apps is a great application','app',1,2) FROM DUAL;

8. LPAD

The Lpad function pads the character value right-justified to a total width of n character positions.

Syntax: lpad(column, n, 'string');

SELECT lpad('100',5,'x') FROM DUAL;

9. RPAD

The Rpad function pads the character value left-justified to a total width of n character positions.

Syntax: rpad(column, n, 'string');

SELECT rpad('100',5,'x') FROM DUAL;

10. TRIM

The Trim function removes the leading or trailing or both the characters from a string.

Syntax: trim(leading|trailing|both, trim_char from trim_source)

SELECT trim('O' FROM 'ORACLE') FROM DUAL;

11. REPLACE

The Replace function is used to replace a character with another character in a string.

Syntax: replace(column, old_char,new_char)

SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;