These functions operate on single rows only and return one result per row. There are different types of single-row functions.

Character

Number

Date

Conversion

Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query. Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested.

Syntax

function_name [(arg1, arg2,...)]

function_name is the name of the function.

arg1, arg2 is any argument to be used by the function. This can be represented by a column name or expression.

Types of Single Row Function

1) Character functions:-
Accept character input and can return both character and number values.

They are of two types: -

a) Case-manipulation functions. EX:- LOWER, UPPER, INITCAP

b) Character-manipulation functions. EX-:- CONCAT, SUBSTR, LENGTH, INSTR, LPAD | RPAD, TRIM, REPLACE

Queries that shows the use of case-manipulation functions:-

SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;

The above query used the two case-manipulation functions i.e. UPPER(last name) which shows all the last name in uppercase and LOWER(jobid) which shows all job id in lower case. The result of the above query is like this:-

Column name = EMPLOYEE DETAILS

and rows are like this The job id for JOSHI is android_developer.

Suppose you want to display the employee number, first name and department name of the employee whose last name is "joshi" and you don't its case either it is in lower case or in upper case, then use this query to fetch the result

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = joshi;

2) Character-Manipulation Functions

a) CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)
Example:-
CONCAT(Hello, World) => HelloWorld
b) SUBSTR: Extracts a string of determined length
Example:-
SUBSTR(HelloWorld,1,5) => Hello
c) LENGTH: Shows the length of a string as a numeric value
Example:
LENGTH(HelloWorld) => 10
d) INSTR
: Finds numeric position of a named character
Example:
INSTR(HelloWorld, W) => 6
e) LPAD: Pads the character value right-justified
Example:
LPAD(salary,10,a) => aaaaa24000
f) RPAD: Pads the character value left-justified
Example:
RPAD(salary, 10, a) => 24000aaaaa
g) TRIM: Trims heading or trailing characters (or both) from a character string (If trimcharacter or trimsource is a character literal, you must enclose it in single quotes.)
Example:
TRIM(H FROM HelloWorld) => elloWorld

SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, a) "Contains a?"
FROM employees
WHERE SUBSTR(job_id, 4) = REP;

The above query displays employee first names and last names joined together, the length of the employee last name, and the numeric position of the letter a in the employee last name for all employees who have the string REP contained in the job ID starting at the fourth position of the job ID.

Hope you like this blog :). Rest of the single value functions will be covered in next blog.
References: Oracle docs